mysql基础知识

sql 语句 （结构化查询语言,structure  query  language) ，在不同数据库都是通用的，但会有少许不同




最早是由ANSI定义的标准   sql-86




DQL(data query language) 数据查询语言   select 
DDL(data define language) 数据定义语言   create  drop  alter  truncate  rename
DML(data manipulate language) 数据操纵语言   insert update delete
DCL(data control language) 数据控制语言   grant revoke



事务控制
commit     提交
        rollback   回滚






DDL   


mysql> create database/schema aaa; --创建一个叫aaa的库，也就是数据目录下创建了一个aaa的目录，owner为mysql.mysql，并在其内有一个db.opt的默认字符集文件
[root@li ~]# ll /mysqldata56/aaa/
-rw-rw---- 1 mysql mysql   59 07-31 15:51 db.opt




----------------------------------------
手动在操作系统建库的过程
# mkdir /mysqldata56/bbb --直接在datadir里建立一个bbb目录
# ll /mysqldata56 --但是看到权限不一样
total 86060
drwx------ 2 mysql mysql     4096 Jul 11 14:28 aaa
drwxr-xr-x 2 root  root      4096 Jul 11 14:30 bbb




mysql> use bbb;
Database changed
mysql> create table bbb (id int); --在数据库里操作，是不能建表的
ERROR 1005 (HY000): Can't create table 'bbb' (errno: 13)


# /usr/local/mysql/bin/perror 13 --使用这个命令可以查访这个错误代码，告诉我们是权限拒绝错误（这种错误代码还可能会出现在错误日志里，你可以使用这种方法排错）
OS error code  13:  Permission denied




# chown mysql.mysql /mysqldata56/bbb/
# chmod 700 /mysqldata56/bbb/ --改owner,group,和权限
# cp -a /mysqldata56/aaa/db.opt /mysqldata56/bbb/ --再从别的库拷贝db.opt文件，并保留原属性


到此，手动建库完成
--------------------------------------






mysql> create database aaa character set=latin1;
--创建库的时候，指定非默认的字符集


mysql> alter database aaa character set=utf8;  
--修改一个库的字符集


# vim /data/aaa/db.opt 
--直接vim打开修改字符集，也可以（需要数据库重启)




-----------------


mysql> show create database aaa --可以用show查看创建库或者表的相关参数


mysql> create database  if not exists aaa --如果不存在则创建，如果存在，则有一个警告
Query OK, 0 rows affected, 1 warning (0.00 sec)


mysql> show warnings;  --使用此命令可以查看上一句命令的警告，注意只能查看上一句
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Note  | 1007 | Can't create database 'aaa'; database exists |




mysql> drop database aaa;
--删除aaa库




--库改名的话没有相关命令，可以直接在系统上mv来修改




----------------------------------------------------------------






mysql> create table emp (ename varchar(20),sex char(1),hiredate date,sal decimal(10,2),deptno tinyint(1));  --创建表




mysql> create table emp
    -> (ename varchar(20),
    -> sex char(1),
    -> hiredate date,
    -> sal decimal(10,2),
    -> deptno tinyint(1)); --可以分开写，比较容易阅读，但注意这只是一行




mysql> create table dept (deptno tinyint(1),deptname varchar(30),location varchar(50)); --再创建一个部门表，注意deptno这一列和emp表的deptno列是对应的






--表的其它操作
mysql> show create table emp;  --查看创建表的参数，引擎和默认的字符集


mysql> alter table emp engine=memory; --修改表的引擎
mysql> alter table emp default charset=utf8;  --修改表的字符集


mysql> drop table emp;


mysql> rename table emp to emp1;   


mysql> create table emp  ( ename varchar(20), sex char(1), hiredate date, sal decimal(10,2), deptno tinyint(1)) engine=memory default charset=utf8; --也可以在创建表时直接指定非默认的引擎或字符集








mysql> alter table emp add  empno int;--增加一个列，默认增加到最后
mysql> alter table emp add  empno int after hiredate; --在hiredate这列后面增加一列，使用after关键字
mysql> alter table emp add empno int first; --把一列加到最前面，使用first关键字


mysql> alter table emp drop empno;  --删除一列






mysql> alter table emp  modify ename varchar(40); --使用modify修改一列的数据类型


mysql> alter table emp  change ename ename varchar(30); --使用change修改一列的数据类型
mysql> alter table emp  change ename name varchar(30);  --修改列名要使用change去修改
--modify和change的区别，都可以修改数据类型，但change要写原列名;只有change可以修改列名，modify不可以




mysql> truncate table emp; --截断表，清空了表内的所有数据，但是表的结构还在






例：简述 drop,delete,truncate的区别?


drop: 属于DDL（在事务控制里，DDL语句不需要commit)，他能删除对象（表，库）


truncate:属于DDL
清大量数据速度快，高水位线（high water mark）下降
delete:属于DML（在事务控制里，DDL语句是需要commit，不提交的话可以rollback）
高水位线不下降






练习：通过上面的命令，把emp表多加三列，成为下面的格式
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| empno     | int(11)       | YES  |     | NULL    |       | 
| ename     | varchar(20)   | YES  |     | NULL    |       | 
| sex       | char(1)       | YES  |     | NULL    |       | 
| birthday  | date          | YES  |     | NULL    |       | 
| hiredate  | date          | YES  |     | NULL    |       | 
| sal       | decimal(10,2) | YES  |     | NULL    |       | 
| deptno    | tinyint(1)    | YES  |     | NULL    |       | 
| managerno | int(11)       | YES  |     | NULL    |       | 
+-----------+---------------+------+-----+---------+-------+




mysql> alter table emp add empno int first;




mysql> alter table emp add birthday date after sex;




mysql> alter table emp add managerno int;




=============================================================






DML insert  delete  update






mysql> insert into aaa.emp values (1,'boss','m','1964-08-08','1995-01-01','20000','1','1')
--插入数据，注意在mysql里数字可以不用加引号，别的都加引号






mysql> insert into emp values 
    -> (1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),
    ->(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1');
Query OK, 2 rows affected, 0 warning (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
--一次插入多条记录






还可以插入特定的列（非所有列），那么没有插入的就成了空值（空值不是0，它做任何运算结果还是空值)


mysql> insert into emp (ename,sex) values ('lisi','m');






下面针对上面的新表结构，写成一个脚本，直接插入9列数据
--也可以直接写成一行的脚本，使用mysql工具直接导入进去




vim emp.sh
#!/bin/bash


insert into aaa.emp values (1,'boss','m','1964-08-08','1995-01-01','20000','1','1'),(2,'zhangsan','m','1967-04-05','1995-04-11','15000','2','1'),(3,'lisi','f','1973-01-28','1998-11-21','13000','3','1'),(4,'wangwu','f','1975-06-03','1999-12-12','12000','4','1'),(5,'maliu','m','1982-08-18','2001-07-03','8000','2','2'),(6,'tianqi','f','1983-02-15','2002-11-01','7000','2','2'),(7,'mark','m','1984-08-12','2003-10-02','6500','3','3'),(8,'john','m','1985-09-14','2005-04-03','6000','3','3'),(9,'mm','f','1990-06-08','2008-09-13','4000','4','4');






上面的脚本导入的方法
方法1：
# /usr/local/mysql/bin/mysql < emp.sh
方法2：
mysql> source /path/emp.sh
方法3：
# cat emp.sh | /usr/local/mysql/bin/mysql






mysql> select * from aaa.emp;
+-------+----------+------+------------+------------+----------+--------+-----------+
| empno | ename    | sex  | birthday   | hiredate   | sal      | deptno | managerno |
+-------+----------+------+------------+------------+----------+--------+-----------+
|     1 | boss     | m    | 1964-08-08 | 1995-01-01 | 20000.00 |      1 |         1 | 
|     2 | zhangsan | m    | 1967-04-05 | 1995-04-11 | 15000.00 |      2 |         1 | 
|     3 | lisi     | f    | 1973-01-28 | 1998-11-21 | 13000.00 |      3 |         1 | 
|     4 | wangwu   | f    | 1975-06-03 | 1999-12-12 | 12000.00 |      4 |         1 | 
|     5 | maliu    | m    | 1982-08-18 | 2001-07-03 |  8000.00 |      2 |         2 | 
|     6 | tianqi   | f    | 1983-02-15 | 2002-11-01 |  7000.00 |      2 |         2 | 
|     7 | mark     | m    | 1984-08-12 | 2003-10-02 |  6500.00 |      3 |         3 | 
|     8 | john     | m    | 1985-09-14 | 2005-04-03 |  6000.00 |      3 |         3 | 
|     9 | mm       | f    | 1990-06-08 | 2008-09-13 |  4000.00 |      4 |         4 | 
+-------+----------+------+------------+------------+----------+--------+-----------+
9 rows in set (0.00 sec)








把dept表也插入记录，方便下面的实验
mysql> insert into dept values
    -> (1,'manager','beijing'),
    -> (2,'it','shenzhen'),
    -> (3,'sale','shanghai'),
    -> (4,'services','guangzhou');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0




mysql> select * from dept;
+--------+----------+-----------+
| deptno | deptname | location  |
+--------+----------+-----------+
|      1 | manager  | beijing   | 
|      2 | it       | shenzhen  | 
|      3 | sale     | shanghai  | 
|      4 | services | guangzhou | 
+--------+----------+-----------+
4 rows in set (0.00 sec)








===============================================================
update




把wangwu性别改成m
mysql> update emp set sex='m' where ename='wangwu';




wangwu的工资加500


mysql> update emp set sal=sal+500  where ename='wangwu';




mark由3号部门换成2号部门，同时工资加1000,上级管理者也改为2号部门的头
mysql> update emp set deptno='2' and sal=sal+1000 and managerno=2 where ename='mark'; --错误写法


mysql> update emp set deptno='2',sal=sal+1000,managerno=2 where ename='mark'; --正确写法






2号部门的工资涨10%
mysql> update emp set sal=sal*1.1  where deptno=2;






工资就john和mark涨10%,其它人不涨
mysql> update emp set sal=sal*1.1 where ename='john' or  ename='mark';
mysql> update emp set sal=sal*1.1 where ename in ('john','mark');




工资都涨10%，john和mark犯错误，就他们不涨
mysql> update emp set sal=sal*1.1 where ename!='john'  and  ename<>'mark';
mysql> update emp set sal=sal*1.1 where ename not in ('john','mark');






delete


delete from emp where  xxxxx;  




--------------------------------------------------------------






DQL  


select


mysql> select * from emp; --* 代表查看所有列


mysql> select ename,sal from emp; -- 只查看其中几列


mysql> select ename,sal*2 as "double sal" from emp; --把工资两倍做一个别名


mysql> select distinct deptno from emp;  --distinct 去除重复行






查询所有男员工的姓名和工资
mysql> select ename,sal from emp where sex='m';




查询工资大于8000的员工的所有信息


mysql> select * from emp where sal>8000;




查询工资在4000到8000之间的员工的所有信息（包含4000和8000的)
mysql> select * from emp where sal>=4000 and  sal<=8000;
mysql> select * from emp where sal between 4000 and 8000;




查询入职时间在2001那年的员工的姓名和工资
mysql> select ename,sal from emp where year(hiredate)=2001;
mysql> select ename,sal from emp where substr(hiredate,1,4)=2001;
mysql> select ename,sal from emp where hiredate>'2000-12-31' and  hiredate<'2002-01-01';
mysql> select ename,sal from emp where hiredate>='2001-01-01' and hiredate <= '2001-12-31';
mysql> select ename,sal from emp where hiredate like '2001%';
mysql> select ename,sal from emp where hiredate between '2001-01-01' and '2001-12-31';




查询2002年之后(包括2002年)入职的，并且工资大于8000的员工姓名
mysql> select ename from emp where year(hiredate)>=2002 and sal>8000;




－－－－－－－－－－－－－－－－－－－－－－－－－－




排序操作统计工资总额，最大工资，最小工资，平均工资
mysql> select * from emp order by sal; --以工资排序，默认升序排序
mysql> select * from emp order by sal asc; --加不加asc都是升序


mysql> select * from emp order by sal desc; --desc表示降序排序


mysql> select * from emp order by sex,sal;  
--先按性别排，再按工资排。结果是女的都在一起，以工资从小到大排。男的都在一起，以工资从小到大排。
mysql> select * from emp order by sex desc,sal desc;




TOP－N问题
找出工资最低的三个人的姓名和工资
mysql> select * from emp order by sal limit 3;


找出工资最高的三个人的姓名和工资
mysql> select * from emp order by sal desc limit 3;


找出工资最低的女员工的姓名和工资
mysql> select ename,sal from emp where sex='f' order by sal limit 1;




找出工资从高到低第三到第五的人的姓名和工资
mysql> select * from emp order by sal desc limit 2,3;




------------------------------------------------


聚合和分组操作：


mysql> select count(*) from emp; --统计记录条数


mysql> select count(distinct deptno) from emp;
+------------------------+
| count(distinct deptno) |
+------------------------+
|                      4 | 
+------------------------+
1 row in set (0.00 sec)


mysql> select count(distinct deptno) deptcount from emp; --别名
+-----------+
| deptcount |
+-----------+
|         4 | 
+-----------+






统计每个部门的人数
mysql> select deptno,count(*) from emp group by deptno;


mysql> select ename,deptno,count(*) from emp group by deptno;
--这种在mysql里可以查，但无意义，在oracle里属于错误语法。因为前面select的列名除了count(),max(),min(),avg()，sum()等外，别的列名都必须在group by里




统计男，女员工各有多少人
mysql> select sex,count(*) from emp group by sex;




统计每个部门里男女员工各有多少个
mysql> select deptno,sex,count(*) from emp group by deptno,sex;
mysql> select  sex,deptno,count(*) from emp group by sex,deptno;




查找部门人数大于2的部门号和人数
mysql> select deptno,count(*) from emp group by deptno having count(*)>2;




求每个部门的工资总额，最大工资，最小工资，平均工资
mysql> select deptno,sum(sal),max(sal),min(sal),avg(sal) from emp group by deptno;


------------------------------------------




表链接（多表查询)
查出员工姓名和其对应的工资，部门名，部门所在地，并显示


mysql> select ename,sal,deptname,location from emp,dept where emp.deptno=dept.deptno;




－－－－－－－－－－－－－－－－－－－－－－－－


子查询：
查出比wangwu工资高的人的姓名和工资


mysql> select ename,sal from emp where sal > (select sal from emp where ename='wangwu');






----------------------------------------------------------------


DCL


grant 授权


mysql> grant select,insert on aaa.emp to 'aa'@'localhost' identified by '123';  --授权使aa@localhost用户，对aaa库的emp表拥有select和insert权限
mysql> flush privileges; --刷新权限




mysql> revoke select on aaa.emp from 'aa'@'localhost'; --回收aa@localhost用户对aaa库的emp的select权限
mysql> flush privileges;






远程授权


服务端
mysql> grant all on *.* to 'aa'@'%' identified by '123'; --all代表所有权限，*.*代表所有库的所有表  %代表所有IP
mysql> flush privileges;




客户端


[root@li ~]# /usr/local/mysql/bin/mysql -u aa -h 10.1.1.35 -p123 -P 3307


-p 参数表示密码，－P参数表示端口 ，如果是使用默认的3306就不用改端口






---------------------------------------------------------






函数：


mysql> select user();
mysql> select database();
mysql> select version();


password() 函数


mysql> show variables like '%password%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |   --这里ON了，表示使用的是兼容老密码格式的16位
+---------------+-------+


字符函数：
mysql> select concat(ename,'  salary is ', sal) from emp;


mysql> select left(ename,2) from emp;  --取ename的左两个字符


mysql> select right(ename,2) from emp;  --取ename的右两个字符


mysql> select length(ename) from emp;  --长度函数


mysql> select substr(ename,2,2) from emp;  --从第二个字符开始截取，截取两个字符


mysql> select upper(ename) from emp;  --大写函数


mysql> select lower(ename) from emp; --小写函数


mysql> select upper(lower(ename)) from emp; --函数的嵌套






数学函数


mysql> select mod(10,3);   --取余


mysql> select round(4.444444); --四舍


mysql> select round(4.544444); --五入


日期函数


mysql> select current_time();


mysql> select current_date();


mysql> select current_timestamp();


mysql> select now();


mysql> select year(now());


mysql> select month(now());


mysql> select day(now());


mysql> select hour(now());


mysql> select minute(now());


mysql> select second(now());