mysql数据库语法大全总结

dos命令链接数据库：mysql -u root -p (root为创建时输入的用户名，p后面跟的是密码，输入密码时并不会显示）

建数据库 ：create database 数据库名;

查看存在的数据库：show databases;

使用数据库：use 数据库名;

建表：create table tab_name （列）; 举例：

create table students (id int primary key,name char(20)) default charset=utf8;

primary key是设置主键，具有唯一性不可重复，一张表只有一个主键，default charset=utf8; 这句意思是指这个表设置支持中文！

删表：drop table tablename;

查看表属性：desc tab_name;

插入数据方法一：insert into tab_name values（数据）; 举例：

insert into students values (1,"二狗");

插入数据方法二：insert into tab_name (列名） values（数据）; 举例：

insert into students (id,name) values (1,"二狗");

表已创建，添加主键：alter table tab_name add primary key(指定列名）;
举例：

alter table tab_name add primary key(id);

删除主键：alter table tab_name drop primary key;

修改表名：alter table tab_name rename to new-tab_name; 举例：

alter table students rename to teachers;

修改列名：alter table change co-name new-co-name +数据类型;
举例：

alter table change name studenName char(10);

添加列：alter table tab_name add （column） co-name +数据类型;
举例：

alter table students add (column) age int(5);

添加/修改数据类型 ：alter table tab_name modify co-name 数据类型;
举例：

alter table students modify name varchar(25);

查询表：select * from tab_name;

查询表的某些字段：select 字段名 from tab_name;

条件查询：select*from tab_name where +条件; 举例：

select * from students where id = 1;

排序：order by 倒序：order by desc 举例：

select * from students order by id;  # 根据id排序查询

限制查询数量：limit

select * from students order by id limit 1; # 正序查询第一条

查询从第几条开始的数量

select * from students order by id limit 1,5;

分组查询：group by

select * from students group by name;   # 根据姓名分组排序

去重复查询：distinct

select distinct name from students;  # 查询不同的名字

查询数量：count

select count(*) from students; # 查询共有多少数据

查询最大值：max

select max(id) from students;

查询最小值：min

select min(id) from students;

查询平均数：avg

select avg(id) from students;

查询总和：sum

select sum(id) from students;

两个表联查–左连接：left join

select * from students as a left join teachers as b on a.id = b.id;

表名后面加as，是指临时给他一个别名;on后面跟的是条件语句，两个表的id是通用的，需要用id来做桥梁链接两个表；

两个表联查–右连接：right join

select * from students as a right join teachers as b on a.id = b.id;

两个表联查–内连接：inner join （inner可省略）

select * from students as a join teachers as b on a.id = b.id;

两个表联查-全连接：full join

select * from students as a full join teachers as b on a.id = b.id;

嵌套查询：

select * from (select * from students);

修改数据：update table_name set col = value；注意：update使用时记得加条件，避免造成整个表的数据被修改！

update students set name = '小张' where id = 1;

删除数据：delete from table_name;

delete from students where id = 1;