登陆与退出
cmd状态下：mysql -h 服务器ip地址 -P 端口号 -u 用户名 -p 密码
                quit|exit|\q

1 show global variables like "%datadir%";

 1 1、创建数据库(在磁盘上创建一个对应的文件)
 2     create database [if not exists(判断是否存在，存在不继续创建数据库)] db_name [character set xxx(设置编码格式)]
 3 
 4 2、查看数据库
 5     show databases;  # 查看所有数据库
 6     show create database db_name;  # 查看数据库的创建方式
 7 
 8 3、修改数据库
 9     alter database db_name [character set xxx]
10 
11 4、删除数据库
12     drop database [if exists] db_name;
13 
14 5、使用数据库:用于切换数据库
15     use db_name;
16 
17 6、查看当前使用的数据库
18     select database();

 1 格式:
 2     create table table_name(
 3         field1 type[完整性约束条件],
 4         field2 type,
 5         ...
 6     )[character set xxx];
 7 
 8 
 9 创建一个员工表
10     create table employee(
11         id int primary key auto_increment,
12         name varchar(20),
13         gender char(1),
14         birthday date,
15         entry_date date,
16         job varchar(20),
17         salary double(4,2) unsigned,
18         resume text   -- 注意，最后一个字段不加","
19     )
20 
21 /*
22 约束:
23 primary key (非空且唯一)：能够唯一区分出当前记录的字段称为主键
24 unique
25 not null
26 auto_increment  主键字段必须是数字类型
27 foreign key   外键约束
28 */

 1 增加列字段:
 2     alter table tab_name add [column] 列名 类型 [完整性约束条件] [first|after 字段名]
 3     alter table user add varchar(20) not null unique first/after username;
 4 
 5 
 6 添加多个字段:
 7     alter table users2
 8         add addr varchar(20),
 9         add age int first,
10         add birth    varchar(20) after name;
11 
12 
13 修改一列类型:
14     alter table tab_name modify 列名 类型 [完整性约束条件] [first | after 字段名];
15     alter table users2 change age Age int default 28 first;
16 
17 
18 删除一列:
19     alter table tab_name drop [column] 列名;
20 
21 
22 修改表名:
23     rename table 旧表名 to 新表名;
24 
25 
26 删除表:
27     drop table tab_name;
28 
29 
30 删除数据库:
31     drop database 数据库名;
32 
33 
34 添加主键和删除主键:
35     alter table tab_name add primary key(字段名称,...);
36     alter table users drop primary key;

 1 -- 1.增加一条记录insert
 2 
 3       /*insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);*/
 4 
 5 
 6       create table employee_new(
 7                  id int primary key auto_increment,
 8                  name varchar(20) not null unique,
 9                  birthday varchar(20),
10                  salary float(7,2)
11                              );
12 
13        insert into employee_new (id,name,birthday,salary) values
14                      (1,\'yuan\',\'1990-09-09\',9000);
15 
16        insert into employee_new values
17        (2,\'alex\',\'1989-08-08\',3000);
18 
19        insert into employee_new (name,salary) values
20        (\'xialv\',1000);
21 
22       -- 插入多条数据
23        insert into employee_new values
24        (4,\'alvin1\',\'1993-04-20\',3000),
25        (5,\'alvin2\',\'1995-05-12\',5000);
26 
27       -- set插入: insert ［into］ tab_name set 字段名=值
28 
29       insert into employee_new set id=12,name="alvin3";
30 
31 
32 -- 2.修改表记录  update tab_name set field1=value1,field2=value2,......[where 语句]
33 
34     /*      UPDATE语法可以用新值更新原有表行中的各列。
35             SET子句指示要修改哪些列和要给予哪些值。
36             WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。*/
37 
38 
39     update employee_new set birthday="1989-10-24" WHERE id=1;
40 
41     --- 将yuan的薪水在原有基础上增加1000元。
42     update employee_new set salary=salary+4000 where name=\'yuan\';
43 
44 -- 3.删除表纪录
45 
46      delete from tab_name [where ....]
47 
48         /*    如果不跟where语句则删除整张表中的数据
49             delete只能用来删除一行记录
50             delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
51             TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
52             事务中恢复。*/
53 
54                 -- 删除表中名称为’alex’的记录。
55                 delete from employee_new where name=\'alex\';
56                 -- 删除表中所有记录。
57                 delete from employee_new;-- 注意auto_increment没有被重置:alter table employee auto_increment=1;
58                 -- 使用truncate删除表中记录。
59                 truncate table emp_new;

  1 -- 查询表达式
  2 
  3    SELECT *|field1,filed2 ...   FROM tab_name
  4                   WHERE 条件
  5                   GROUP BY field
  6                   HAVING 筛选
  7                   ORDER BY field
  8                   LIMIT 限制条数
  9 
 10 
 11  ---准备表
 12 
 13    CREATE TABLE ExamResult(
 14 
 15    id INT PRIMARY KEY  auto_increment,
 16    name VARCHAR (20),
 17    JS DOUBLE ,
 18    Django DOUBLE ,
 19    OpenStack DOUBLE
 20 );
 21 
 22 
 23 INSERT INTO ExamResult VALUES  (1,"yuan",98,98,98),
 24                                (2,"xialv",35,98,67),
 25                                (3,"alex",59,59,62),
 26                                (4,"wusir",88,89,82),
 27                                (5,"alvin",88,98,67),
 28                                (6,"yuan",86,100,55);
 29 
 30 
 31 -- （1）select [distinct] *|field1，field2，......   from tab_name
 32             -- 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列
 33             -- 表明确指定要查找的列，distinct用来剔除重复行。
 34 
 35                     -- 查询表中所有学生的信息。
 36                     select * from ExamResult;
 37                     -- 查询表中所有学生的姓名和对应的英语成绩。
 38                     select name,JS from ExamResult;
 39                     -- 过滤表中重复数据。
 40                     select distinct JS ,name from ExamResult;
 41 
 42 
 43 
 44 -- （2）select 也可以使用表达式，并且可以使用: 字段 as 别名或者:字段 别名
 45 
 46                 -- 在所有学生分数上加10分特长分显示。
 47 
 48                 select name,JS+10,Django+10,OpenStack+10 from ExamResult;
 49                 -- 统计每个学生的总分。
 50                 select name,JS+Django+OpenStack from ExamResult;
 51                 -- 使用别名表示学生总分。
 52                 select name as 姓名,JS+Django+OpenStack as 总成绩 from ExamResult;
 53                 select name,JS+Django+OpenStack 总成绩 from ExamResult;
 54 
 55                 select name JS from ExamResult; -- what will happen?---->记得加逗号
 56 
 57 -- （3）使用where子句，进行过滤查询。
 58 
 59             -- 查询姓名为XXX的学生成绩
 60             select * from ExamResult where name=\'yuan\';
 61             -- 查询英语成绩大于90分的同学
 62             select id,name,JS from ExamResult where JS>90;
 63             -- 查询总分大于200分的所有同学
 64             select name,JS+Django+OpenStack as 总成绩 from
 65                         ExamResult where JS+Django+OpenStack>200 ;
 66             -- where字句中可以使用：
 67                      -- 比较运算符：
 68                         > < >= <= <> !=
 69                         between 80 and 100 值在10到20之间
 70                         in(80,90,100) 值是10或20或30
 71                         like \'yuan%\'
 72                         /*
 73                         pattern可以是%或者_，
 74                         如果是%则表示任意多字符，此例如唐僧,唐国强
 75                         如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
 76                         */
 77 
 78                     -- 逻辑运算符
 79                         在多个条件直接可以使用逻辑运算符 and or not
 80             -- 练习
 81                 -- 查询JS分数在 70－100之间的同学。
 82                 select name ,JS from ExamResult where JS between 80 and 100;
 83                 -- 查询Django分数为75,76,77的同学。
 84                 select name ,Django from ExamResult where Django in (75,98,77);
 85                 -- 查询所有姓王的学生成绩。
 86                 select * from ExamResult where name like \'王%\';
 87                 -- 查询JS分>90，Django分>90的同学。
 88                 select id,name from ExamResult where JS>90 and Django >90;
 89                 -- 查找缺考数学的学生的姓名
 90                 select name from ExamResult where Database is null;
 91 
 92 
 93 -- （4）Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
 94 
 95               -- select *|field1,field2... from tab_name order by field [Asc|Desc]
 96 
 97               -- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
 98               -- 练习：
 99               -- 对JS成绩排序后输出。
100               select * from ExamResult order by JS;
101               -- 对总分排序按从高到低的顺序输出
102               select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
103                    总成绩 from ExamResult order by 总成绩 desc;
104               -- 对姓李的学生成绩排序输出
105               select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))
106                    总成绩 from ExamResult where name like \'a%\'
107                                          order by 总成绩 desc;
108 
109 
110 -- （5）group by 分组查询：
111         CREATE TABLE order_menu(
112              id INT PRIMARY KEY auto_increment,
113              product_name VARCHAR (20),
114              price FLOAT(6,2),
115              born_date DATE,
116              class VARCHAR (20)
117                                 );
118 
119 
120          INSERT INTO order_menu (product_name,price,born_date,class) VALUES
121                                              ("苹果",20,20170612,"水果"),
122                                              ("香蕉",80,20170602,"水果"),
123                                              ("水壶",120,20170612,"电器"),
124                                              ("被罩",70,20170612,"床上用品"),
125                                              ("音响",420,20170612,"电器"),
126                                              ("床单",55,20170612,"床上用品"),
127                                              ("草莓",34,20170612,"水果");
128 
129 
130             -- 注意,按分组条件分组后每一组只会显示第一条记录
131 
132             -- group by字句，其后可以接多个列名，也可以跟having子句,对group by 的结果进行筛选。
133 
134 
135                     -- 按位置字段筛选
136                        select * from order_menu group by 5;
137 
138                     -- 练习：对购物表按类名分组后显示每一组商品的价格总和
139                        select class,SUM(price)from order_menu group by class;
140 
141                     -- 练习：对购物表按类名分组后显示每一组商品价格总和超过150的商品
142                        select class,SUM(price)from order_menu group by class
143                                                         HAVING SUM(price)>150;
144 
145 
146 
147                    /*
148                    having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
149                      <1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
150                      <2>使用where语句的地方都可以用having进行替换
151                      <3>having中可以用聚合函数，where中就不行。
152                    */
153 
154 
155             -- GROUP_CONCAT() 函数
156             SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;
157 
158 
159 -- （6）聚合函数： 先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。
160                  --   (一般和分组查询配合使用)
161 
162         --<1> 统计表中所有记录
163 
164             -- COUNT(列名)：统计行的个数
165                     -- 统计一个班级共有多少学生？先查出所有的学生，再用count包上
166                      select count(*) from ExamResult;
167                     -- 统计JS成绩大于70的学生有多少个？
168                      select count(JS) from ExamResult where JS>70;
169                     -- 统计总分大于280的人数有多少？
170                      select count(name) from ExamResult
171                            where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;
172                     -- 注意:count(*)统计所有行;     count(字段)不统计null值.
173 
174             -- SUM(列名)：统计满足条件的行的内容和
175                     -- 统计一个班级JS总成绩？先查出所有的JS成绩，再用sum包上
176                         select JS as JS总成绩 from ExamResult;
177                         select sum(JS) as JS总成绩 from ExamResult;
178                     -- 统计一个班级各科分别的总成绩
179                         select sum(JS) as JS总成绩,
180                                sum(Django) as Django总成绩,
181                                sum(OpenStack) as OpenStack from ExamResult;
182 
183                     -- 统计一个班级各科的成绩总和
184                         select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
185                                                     as 总成绩 from ExamResult;
186                     -- 统计一个班级JS成绩平均分
187                         select sum(JS)/count(*) from ExamResult ;
188                     -- 注意：sum仅对数值起作用，否则会报错。
189 
190             -- AVG(列名)：
191                             -- 求一个班级JS平均分？先查出所有的JS分，然后用avg包上。
192                                 select avg(ifnull(JS,0)) from ExamResult;
193                             -- 求一个班级总分平均分
194                                 select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
195                                                          from ExamResult ;
196             -- Max、Min
197                             -- 求班级最高分和最低分（数值范围在统计中特别有用）
198                               select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
199                               最高分 from ExamResult;
200                               select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
201                               最低分 from ExamResult;
202                             -- 求购物表中单价最高的商品名称及价格
203                               ---SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗?
204                               
205                               SELECT MAX(price) FROM order_menu; 
206 
207                             -- 注意：null 和所有的数计算都是null，所以需要用ifnull将null转换为0！
208                             --      -----ifnull(JS,0)
209 
210 
211                 -- with rollup的使用
212 
213             --<2> 统计分组后的组记录
214 
215 
216 -- （7） 重点：Select from where group by having order by
217                 -- Mysql在执行sql语句时的执行顺序：
218                 -- from  where  select group by  having order by
219                 -- 分析:
220                 select JS as JS成绩 from ExamResult where JS成绩 >70; ---- 不成功
221                 select JS as JS成绩 from ExamResult having JS成绩 >90; --- 成功
222 
223 --  (8) limit
224                 SELECT * from ExamResult limit 1;
225                 SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录
226                 SELECT * from ExamResult limit 2,2;
227 
228 
229 --- (9) 使用正则表达式查询
230         SELECT * FROM employee WHERE emp_name REGEXP \'^yu\';
231 
232         SELECT * FROM employee WHERE emp_name REGEXP \'yun$\';
233 
234         SELECT * FROM employee WHERE emp_name REGEXP \'m{2}\';

 1 -- 子查询是将一个查询语句嵌套在另一个查询语句中。
 2 -- 内层查询语句的查询结果，可以为外层查询语句提供查询条件。
 3 -- 子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
 4 -- 还可以包含比较运算符：= 、 !=、> 、<等
 5 
 6 
 7 -- 1. 带IN关键字的子查询
 8 
 9    ---查询employee表，但dept_id必须在department表中出现过
10 
11    select * from employee
12             where dept_id IN
13             (select dept_id from department);
14 
15 
16 +--------+----------+------+---------+
17 | emp_id | emp_name | age  | dept_id |
18 +--------+----------+------+---------+
19 |      1 | A        |   19 |     200 |
20 |      2 | B        |   26 |     201 |
21 |      3 | C        |   30 |     201 |
22 |      4 | D        |   24 |     202 |
23 |      5 | E        |   20 |     200 |
24 +--------+----------+------+---------+
25 rows in set (0.01 sec)
26 
27 
28 
29 -- 2. 带比较运算符的子查询
30       --      =、!=、>、>=、<、<=、<>
31 
32      -- 查询员工年龄大于等于25岁的部门
33      select dept_id,dept_name from department
34            where dept_id IN
35           (select DISTINCT dept_id from employee where age>=25);
36 
37 -- 3. 带EXISTS关键字的子查询
38 
39 -- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
40 -- 而是返回一个真假值。Ture或False
41 -- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
42 
43      select * from employee
44               WHERE EXISTS
45               (SELECT dept_name from department where dept_id=203);
46 
47       --department表中存在dept_id=203，Ture
48 
49 
50      select * from employee
51                 WHERE EXISTS
52               (SELECT dept_name from department where dept_id=205);
53 
54      -- Empty set (0.00 sec)
55 
56 
57     ps:  create table t1(select * from t2);