mysql基础语法演示_5分钟带你快速回顾MySQL基础语法

MySQL

1.安装

略

2.数据库操作

查看所有数据库

show databases;

# mysql 用分号结束语句

创建数据库

# create database [name]

create database test

删除数据库

drop database test

使用数据库

# use [name]

use test;

创建表

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;

字段约束

主键 primary key:用来唯一标识一条记录的字段

非空 not null：所修饰字段值不能为空

外键 foreign key：外键标识当前字段的取值只能来自于外键所指定的字段中的值，或者为null

自增长 auto_increment：插入数据时自动增长的字段

# create table department

create 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

修改内容关键词语法

添加列addalter table [表名] add [列名] [类型] [约束]

修改列modifyalter table [表名] modofy [列名] [类型] [约束]

列重命名changealter table [表名] change [旧列名] [ 新列名] [类型] [约束]

删除列dropalter table [表名] drop [列名]

表重命名renamerename table [旧表名] to [新表名]

修改表的字符集character setalter 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');

insert 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:主机地址

#添加用户

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'