MySQL的基本操作

1.创建用户表
CREATE TABLE USER
(
id INT NOT NULL,
name VARCHAR(10) NOT NULL,
sign_in INT NOT NULL,
date DATE,
PRIMARY KEY(id)
);
2.插入用户
INSERT INTO USER (id,name,sign_in,date) VALUES(1,'zzy',5,now());
3.删除用户
DELETE FROM USER WHERE id = 3;
4.修改用户
UPDATE USER SET sign_in = 8 WHERE name = 'zcl';
(当判断条件是NULL的时候使用IS NULL而不是=NULL)
5.查询用户
（1）AS <String>是用别名返回这一列的表
SELECT NAME AS ACCOUNT FROM USER;
（2）DISTINCT 消除值相同的元组，只显示一个
SELECT DISTINCT NAME AS ACCOUNT FROM USER;
（3）ALL 则不消除重复，也是默认方法
SELECT ALL NAME AS ACCOUNT FROM USER;
（4）用LIKE查询
①'%a'     //以a结尾的数据
SELECT NAME FROM USER WHERE NAME LIKE '%Y';
'a%'     //以a开头的数据
mysql> SELECT NAME FROM USER WHERE NAME LIKE 'Z%';
'%a%'    //含有a的数据
mysql> SELECT NAME FROM USER WHERE NAME LIKE '%Z%';
'_a_'    //三位且中间字母是a的
mysql> SELECT NAME FROM USER WHERE NAME LIKE '_Z_';
'_a'     //两位且结尾字母是a的
mysql> SELECT NAME FROM USER WHERE NAME LIKE '_Z';
'a_'     //两位且开头字母是a的
mysql> SELECT NAME FROM USER WHERE NAME LIKE 'Z_';
6.UNION合并查询
（1）创建新用户表
CREATE TABLE NEWUSER
(
id INT NOT NULL,
name VARCHAR(10) NOT NULL,
country VARCHAR(5),
sign_in INT NOT NULL,
PRIMARY KEY(id)
);
(2)联合查询（UNION）
①DISTINCT和ALL是针对整条元组的，如果其中有些列的值相同而不是每一个列都相同是不会被消除的
②联合查询所搜寻的两个表的列数必须相同且一致
 select name,sign_in from user
 union distinct（默认DISTINCT）
 select name,sign_in from newuser;
7.ORDER BY的使用
①升序ASC 降序DESC，默认升序
select * from user order by sign_in;
8.GROUP BY的使用
①GROUP BY <列名>是把列名相同的元组放在一组，通常和统计函数AVG、SUM 、COUNT等一起配合使用


（1）统计用户名相同的元组各有多少条
SELECT NAME,COUNT(*)FROM USER GROUP BY NAME;
或者
SELECT NAME,COUNT(*)FROM USER GROUP BY NAME;
（2）统计各相同用户名的用户sign_in的总数是多少
SELECT NAME,SUM(SIGN_IN) FROM USER GROUP BY NAME; 
（3）统计各相同用户名的用户sign_in的平均值是多少
SELECT NAME,AVG(SIGN_IN) FROM USER GROUP BY NAME; 
（4）统计各相同用户名的用户sign_in的最大（最小）是多少
SELECT NAME,MAX(SIGN_IN) FROM USER GROUP BY NAME; 
SELECT NAME,MIN(SIGN_IN) FROM USER GROUP BY NAME; 
9.表的连接JOIN
如果有命名相同的列，就用表名.列名加以区分
（1）内连接INNER JOIN
①select user.name,country,date 
from user,newuser
where user.name=newuser.name;
②select user.name,country,date
from user inner join newuser
on user.name=newuser.name;
（2）左连接（会把左边表的所有元组都读进来，即使右边压根没有和他名字一样的，也没有相关得列，全部变成NULL读进来）
select user.name,country,date
from user left join newuser
on user.name=newuser.name;
（3）右连接同理
select newuser.name,country,date
from user right join newuser
on user.name=newuser.name;
10.对于NULL字段的处理
（1）不能使用！=NULL =NULL去查找NULL值
（2）IS NULL IS NOT NULL可用与查找NULL值
11.正则表达式
（1）[.\n]匹配除了\n以外的任何字符
（2）[...]...代表多个字符，表示匹配这些字符中的字符
①匹配包含z的所有元组 SELECT * FROM USER WHERE NAME REGEXP '[Z]';
（3）[^...]表示匹配除了这些字符以外的字符
①注意区别^放在[]里面和外面的区别，放在里面作非的意思，放在外面作开头的意思
（4）.|.|. 或
（5）* >=0
（6）+ >=1
（7）{n}确定匹配n次
（8）{n,m}匹配最少n次最多m次
（9）^匹配输入字符串开始的位置（顺序也必须一致才能匹配，cz就匹配不到）
SELECT * FROM USER WHERE NAME REGEXP '^zc';
（10）$匹配输入字符串结束的位置
SELECT * FROM USER WHERE NAME REGEXP 'zy$';
11.MySQL的事务
（1）MySQL中只有Innodb数据库引擎的数据库或者表才支持事务
（2）事务用来管理insert、update、delete语句
（3）MySQL默认的是事务自动提交
①SET AUTOCOMMIT=0 禁止自动提交
②SET AUTOCOMMIT=1 开启自动提交
（4）事务满足ACID，其中I有四个隔离级别，四个隔离级别通过数据库锁机制实现，是为了防止三种错误的发生
（5）事务控制语句
①BEGIN或者START TRANSACTION：显示开始一个事务
②COMMIT 提交事务
③ROLLBACK 回滚并撤销所有未提交的修改
④SET TRANSACTION：设置事务隔离级别
12.使用ALTER对数据表字段进行修改
（1）删除列（如果只剩下最后一个列则不能删除）
ALTER TABLE USER DROP DATE;
（2）增加列
ALTER TABLE USER ADD DATE DATE;
①用FIRST可以让字段位于第一列 
ALTER TABLE USER ADD DATE DATE FIRST;
②用AFTER <列名>可以让新增的字段位于某个已知字段之后
ALTER TABLE USER ADD DATE DATE AFTER NAME;
（3）修改字段类型
ALTER TABLE USER MODIFY NAME CHAR(10);
（4）修改字段名和类型
ALTER TABLE USER CHANGE NAME NAMES VARCHAR(10);
（5）修改字段可以顺便指定非空和默认值
ALTER TABLE USER MODIFY SIGN_IN BIGINT NOT NULL DEFAULT 5;
（6）单独修改字段默认值
ALTER TABLE USER ALTER SIGN_IN SET DEFAULT 6;
（7）删除字段默认值
ALTER TABLE USER ALTER SIGN_IN DROP DEFAULT;
（8）修改数据表类型（INNODB和MYISAM）
ALTER TABLE USER ENGINE = MYISAM;
（9）查看数据表信息 SHOW TABLE STATUS LIKE 'USER'\G;
（10）修改表名
ALTER TABLE NEWUSER RENAME TO NEW_USER;
（11）删除外键约束 ALTER TABLE <tablename> DROP FORIGN KEY <keyname>