本文共 3581 字，大约阅读时间需要 11 分钟。

快速回顾MySQL基础语法

1.安装

略

2.数据库操作

• 查看所有数据库

  show databases;# mysql 用分号结束语句

• 创建数据库

  # create database [name]create database test

• 删除数据库

  drop database test

• 使用数据库

  # use [name]use test;

3.表操作

• 创建表

  create table test(    id int,    name varchar(10),    age int,    gender char(2),    birthday date,    tel char(11),    email varchar(30));

• 删除表

  # drop table [name]drop table test;

• 字段约束

  1. 主键 primary key:用来唯一标识一条记录的字段
  2. 非空 not null：所修饰字段值不能为空
  3. 外键 foreign key：外键标识当前字段的取值只能来自于外键所指定的字段中的值，或者为null
  4. 自增长 auto_increment：插入数据时自动增长的字段

  # create table departmentcreate table department(    id int primary key auto_increment,    name varchar(30) not null,    tel char(11));

  CREATE TABLE class (id int NOT NULL AUTO_INCREMENT,name varchar(10) DEFAULT NULL,depId int DEFAULT NULL,PRIMARY KEY (id),FOREIGN KEY (id) REFERENCES department(id) ON DELETE CASCADE ON UPDATE RESTRICT);

• 查看当前数据库的所有表

  show tables;

• 查看表结构

  # desc [table_name]desc class;

• 修改表 alter table

  修改内容	关键词	语法
  添加列	add	alter table [表名] add [列名] [类型] [约束]
  修改列	modify	alter table [表名] modofy [列名] [类型] [约束]
  列重命名	change	alter table [表名] change [旧列名] [ 新列名] [类型] [约束]
  删除列	drop	alter table [表名] drop [列名]
  表重命名	rename	rename table [旧表名] to [新表名]
  修改表的字符集	character set	alter table [表名] character set [字符集名称]

  rename table test to student;alter table student add grade int;alter table student modify id  int primary key auto_increment;alter table student change birthday  birth date;alter table student drop age;

4.数据插入

insert into department(id,name,tel) value(1,'dep1','123456');insert into department(name,tel) value('dep2','123456');insert into department(name) value('dep2');# 这条记录id为3insert into department value(4,'dep3','123456');

5.数据删除

delete from department where id =3;

6.数据更新

update department set name='dep5' where id=4;# 使用某一列的值给其他列赋值update department set name=tel;

7.数据查询

#===============单表查询=================# 查询student表中所有字段；select * from student;# 查询student表中特定字段；select id name from student;select name, grade from student;# 查询结果起别名select name 姓名 ,grade 成绩  from student;#查询不重复的姓名select distinct name from student# 查询成绩>60的学生学号，姓名和成绩select id,name,grade from student where grade>60;# 查询总人数select count(*) 总人数 from student;# 查询总人数，总成绩，平均分select count(*) 总人数,sum(grade) 总成绩,sum(grade)/count(*) 平均分  from student;#查询结果排序select * from student order by grade;select * from student order by grade desc;#降序排列# 分组查询##查询男女生人数select gender,count(*) from student group by gender;## 查询各个分数有多少人select grade,count(*) from student group by grade;#===============多表查询=================# 查询班级，系名，电话select class.name, department.name, tel from class ,department  where class.depId=department.id;select c.name, d.name, tel from class c ,department d  where c.depId=d.id;# 使用别名# 左连接select c.name, d.name, tel from class c left join department d on c.depId=d.id ;# 右连接select c.name, d.name, tel from class c right join department d on c.depId=d.id ;

8.用户管理

用户信息在mysql默认数据库mysql.user中保存

#查看用户select user ,host from user;+------------------+-----------+| user             | host      |+------------------+-----------+| mysql.infoschema | localhost || mysql.session    | localhost || mysql.sys        | localhost || root             | localhost |+------------------+-----------+# user :用户名# host:主机地址#添加用户create user 'lisi'@'localhost' identified by '123456';#删除用户drop user 'lisi@localhost';

9.权限管理

用户权限信息在mysql默认数据库mysql.user中保存

#查看用户权限 show grants for 'root'@'localhost'; # 授予用户权限#grant 权限列表 on 数据库.表名 to 'user'@'host'• grant select,update,delete on test.* to 'lisi'@'localhost'grant all on *.* to 'lisi'@'localhost' # 撤销权限授予# revoke 权限列表 on  数据库名.表名 from 'username'@'host'

转载地址：https://console.blog.csdn.net/article/details/105132852 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！