一、数据库操作
1.连接数据库：mysql -u root -p
2.查看已经有的数据库：show databases;
3.新建数据库：create database database_name;
4.删除数据库：drop database database_name;
5.选择数据库：use database_name;
二、表操作
1.查询所有的表：show tables;
2.创建表：create table table_name(需要字段);
3.删除表 ：drop table table_name;
4. 查看表结构：desc table_name;
5.查看建表语句：show create table table_name;
6.修改表名 rename ：alter table old_table_name rename new_table_name;
7.修改字段名和属性 change：alter table table_name change old_table_name new_table_name;
8.添加字段 add：alter table table_name add new_field attr;
9.修改字段顺序 modify：alter table table_name modify field1 attr1 [after field2|first];
10.删除字段 drop：alter table table_name drop field;
三、数据存取
1.插入数据：insert into table_name (field1,field2,.....) value(value1,value2,value3....);
2.查询数据：select [field1,field2|*] from table_name [where condition];
3.修改数据：update table_name set field1=value1,field2=value2 where condition;
4.删除数据：delete from table_name [where condition];
5.备份和还原：备份 mysqldump -u root -p database_name [table_name] > file
还原 mysql -u root -p database_name < file
6.where 条件：=, !=, > ,<, >=, <=, and, or, in, not in, between a and b
四、数据约束
1.唯一性约束 unique key：alter table userinfo change idCard idCard char(18) unique;
2.主键 primary key 自带唯一属性;
3.自增 auto_increment：alter table table_name change field field attr auto_increment;
4.非空约束 not null：alter table table_name change field field attr not null;
5.默认值 default ： alert table table_name change field field attr not null default 'def_value';
6.设置注释 comment ;
7.外键 foreign key：alter table table_name add constraint 外键名 foreign key(field) references 主表名(field) [on delete cascade on update cascade]
on delete cascade //主表删除的时候子表跟着删除
on update cascade //主表更新 子表更新
on delete set null // 主表删 子表设空
五、数据查询
A、单表
1.查询所有列：select * from table_name [where condition];
2.指定查询某一列或多列：select field_name[,field_name2....] from table_name
3.去除重复项 distinct：select distinct phone,class from students;
4.运算 +-*/：select name,score/age as score_age from table_name
5.空值判断 is null：select * from students where phone is null
6.排序 order by：order by field [asc|desc] [,field2 [asc|desc]] ||select * from students order by score desc
7.模糊查询 like：% 代表任意个字符
_ 代表一个任意字符
select * from students where phone like '159%';
8.数量限制 limit：limit n; 从第1行(下标为0)开始取 ，取n条
limit m,n; 从第m条(下标从0开始)取，取n条
select * from students order by score desc limit 0,2;
9.统计函数：
avg(field) 平均值
min(field) 最小值
max(field) 最大值
sum(field) 求和
count(field) 统计条数
10.分组查询 group by：select class,sum(field) from students group by field;
11.分组后条件限制 having：select class,avg(score) as ascore from students group by class having ascore >=60
B、多表
1.连接查询：select field from table1 as t1 inner join table2 as t2 on t1.field=t2.field
2.子查询：select * from students where id in (select s_id from score where score<60 and subject ='语文')
六、索引+存储过程+触发器+事务
索引：http://www.runoob.com/mysql/mysql-index.html
存储过程：
-- delimiter //
-- create procedure test_out(IN s_name char(5),OUT s_age int)
-- BEGIN
-- select age into s_age from students where name = s_name;
-- into 把得到的结果赋值给s_age
-- end//
-- delimiter ;
-- set @age=0; //定义用户变量
-- call test_out('小橙',@age); //使用@age变量接受 out的s_age
-- select @age; //输出变量
触发器
-- create TRIGGER update_count after update on students for each ROW
-- BEGIN
-- update class set number=number+1 where id= new.class;
-- update class set number=number-1 where id= old.class;
-- end;
-- update students set class='102' where id=2007;
事务：【转载】https://www.cnblogs.com/jiangxiaobo/p/7728764.html
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
