mysql数据库、表、行的增删改查语法总结（表格形式列出便于查阅）

一、连接mysql、修改密码

# 连接mysql数据库

mysql  -u root -p123456   # 本地连接mysql

mysql -h 192.168.0.10 -P 3306 -u root -p123456 # 远程连接mysql

 

# 修改密码

set password for root@localhost=password('123456');

 

# 使用root创建用户chushiyan、设置密码，将test库所有权限授予chushiyan：

create user chushiyan identified by 'chushiyan';

grant all on test.* to chushiyan;

 

二、mysql数据库、表、行的增删改查语法总结

	database	table	row
增	①create database test; ②create database test character set GBK; ③create database mydb2 character set GBK collate gbk_chinese_ci;   ④ drop database if exists test; create database test character set utf8 ;	① create table person(  id int primary key  auto_increment,  name varchar(40) unique,  age smallint,  salary double );	①insert into person(id,name,age,salary)values(null,'刘备',40,5000);   ②insert into person values(null,’孔明’,35,4500);   ③insert into person values(null,’关羽’,25,4000),(null,’张飞’,25,4000);
删	① drop database test;   ② drop database if exists test;	①drop table employee;	①delete from person where id=1; ②delete from person; ③truncate table person;
改	① alter database test character set gbk;	①alter table person add image blob; ②alter table person modify name varchar(60); ③alter table person drop age; ④rename table person to user; ⑤alter table user character set gbk; ⑥alter table user change name username varchar(20);	①update person set salary=40000;   ②update person set salary = 60000 where name='刘备';   ③update employee set salary=4000,age=24 where name='关羽';   ④update employee set salary=salary+1000 where name='刘备';
查	①show databases;     ②show create database test;	①desc person;   ②show tables;   ③show create table person;	1、基本查询 ①select * from exam; ②select name,english from exam ③select distinct english from exam; ④select name , math+10,english+10,chinese+10 from exam; ⑤select name ,english+math+chinese from exam; ⑥select name as 姓名 ,english+math+chinese as 总成绩 from exam; ⑦select name 姓名 ,english+math+chinese 总成绩 from exam;   2、使用where过滤查询 ①select * from exam where name='张飞'; ②select * from exam where english > 90; ③select name 姓名,math+english+chinese 总分 from exam where math+english+chinese>230; ④select * from exam where english between 80 and 100; ⑤select * from exam where math in(75,76,77); ⑥select * from exam where name like '张%'; ⑦select * from exam where math>70 and chinese>80;   3、使用order by关键字对查询结果排序 ①select name,chinese from exam order by chinese desc; ②select name 姓名,chinese+math+english 总成绩 from exam order by 总成绩 desc; ③select name 姓名,chinese+math+english 总成绩 from exam where name like '张%' order by 总成绩 desc;   4、聚合函数 （1）count ①select count(*) from exam; ②select count(*) from exam where math>70; ③select count(*)from exam where   math+english+chinese > 230; （2）sum ①select sum(math) from exam; ②select sum(math),sum(english),sum(chinese) from exam; ③select sum(ifnull(chinese,0)+ifnull(english,0)+ ifnull(math,0)) from exam; ④select sum(chinese)/count(*) 语文平均分 from exam;   （3）avg ①select avg(math) from exam; ②select avg(ifnull(chinese,0)+ifnull(english,0)+ ifnull(math,0)) from exam;   （4）max/min ①select max(ifnull(chinese,0)+ifnull(english,0)+ ifnull(math,0)) from exam; ②select min(ifnull(chinese,0)+ifnull(english,0)+ ifnull(math,0)) from exam;     5、分组查询 ①select product,sum(price) from orders group by product; ②select product 商品名,sum(price)商品总价 from orders group by product having sum(price)>100; ③select product from orders where price<100 group by product having sum(price)>150;