MYSQL>>笔记2-1-2

Posted on 2022-02-12 23:28 凡是过去，皆为序曲阅读(159) 评论(0) 编辑收藏举报

mysql> #2.4动手实践
-> create database if not exists mydb;
Query OK, 1 row affected

mysql> #使用数据库mydb
-> use mydb;
Database changed
mysql> create table subscribe(
-> id int primary key,
-> email varchar(50) unique,
-> status int,
-> code varchar(10) not null
-> );
Query OK, 0 rows affected

mysql> #1创建表结构时写约束
-> #2表结构已经存在，修改表结构添加约束
-> alter table subscribe
-> modify code varchar(10);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table subscribe
-> modify code varchar(10) default 'ABCD';
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table subscribe
-> modify code varchar(10);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> #查看表结构
-> desc subscribe;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| email | varchar(50) | YES | UNI | NULL | |
| status | int(11) | YES | | NULL | |
| code | varchar(10) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set

mysql> alter table subscribe
-> modify status int unsigned;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table subscribe
-> modify status int;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> #插入数据
-> #1、所有字段插入（单行数据）
-> #2、指定某几个字段插入（单行数据）
-> #3、所有字段插入（多行数据插入）
-> #4、指定某几个字段插入（多行数据插入）
-> #5、将另一个表中数据全部插入（俩表结构一致）
-> #1、所有字段插入（单行数据）
-> insert into subscribe
-> values(1,'tom123@163.com',1,'TRBXPO');
Query OK, 1 row affected

mysql> insert into subscribe(id,email,status,code)
-> values(2,'lucy123@163.com',1,'LOICPE');
Query OK, 1 row affected

mysql> insert into subscribe(id,email,status,code)
-> values(1,'lily123@163.com',0,'JIXDAMI');
1062 - Duplicate entry '1' for key 'PRIMARY'
mysql> insert into subscribe(id,email,status,code)
-> values(3,'lily123@163.com',0,'JIXDAMI');
Query OK, 1 row affected

mysql> #查询表中数据
-> select * from subscribe;
+----+-----------------+--------+---------+
| id | email | status | code |
+----+-----------------+--------+---------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 0 | JIXDAMI |
+----+-----------------+--------+---------+
3 rows in set

mysql> #2、指定某几个字段插入（单行数据）
-> insert into subscribe
-> (id,email)
-> values(4,'79212343@qq.com');
Query OK, 1 row affected

mysql> #2、指定某几个字段插入（单行数据）
-> insert into subscribe(id,status)
-> values(5,0);
Query OK, 1 row affected

mysql> select * from subscribe;
+----+-----------------+--------+---------+
| id | email | status | code |
+----+-----------------+--------+---------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 0 | JIXDAMI |
| 4 | 79212343@qq.com | NULL | NULL |
| 5 | NULL | 0 | NULL |
+----+-----------------+--------+---------+
5 rows in set

mysql> #3、所有字段插入（多行数据插入）
-> insert into subscribe
-> values(6,'alks@126.com',1,'DFKSDFJ'),(7,'dkff@qq.com',0,'SKDFJ'),(8,'lsdk@126.com',1,'KFDG');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from subscribe;
+----+-----------------+--------+---------+
| id | email | status | code |
+----+-----------------+--------+---------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 0 | JIXDAMI |
| 4 | 79212343@qq.com | NULL | NULL |
| 5 | NULL | 0 | NULL |
| 6 | alks@126.com | 1 | DFKSDFJ |
| 7 | dkff@qq.com | 0 | SKDFJ |
| 8 | lsdk@126.com | 1 | KFDG |
+----+-----------------+--------+---------+
8 rows in set

mysql> #4、指定某几个字段插入（多行数据插入）
-> insert into subscribe(id,email)
-> values(9,'ksdf@qq.com'),
-> (10,'mcv@126.com'),
-> (11,'glh@126.com');
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0

mysql> #5、将另一个表中数据全部插入（俩表结构一致）
-> insert into subscribe1
-> select * from subscribe;
Query OK, 11 rows affected
Records: 11 Duplicates: 0 Warnings: 0

mysql>

mysql> #查询book表中所有列数据
-> select *
-> from book;
+-----+----------------+--------+-------+----------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------------+--------+-------+----------------+----------+-----+
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+----------------+--------+-------+----------------+----------+-----+
7 rows in set

mysql> #查看book表中书名和价格
-> select bname,price from book;
+----------------+-------+
| bname | price |
+----------------+-------+
| 中国时代 | 39 |
| 中国历史的屈辱 | 26 |
| 则也要趁早 | 28 |
| 房间 | 37.6 |
| 平凡的世界 | 75 |
| 心灵鸡汤 | 27 |
| 蜕 | 32 |
+----------------+-------+
7 rows in set

mysql> #列出user表中会员名，积分和会员编号
-> select uname,score,uid from user;
+----------+-------+------+
| uname | score | uid |
+----------+-------+------+
| 何仙姑 | 20 | 1001 |
| 平平人生 | 300 | 1002 |
| 四十不惑 | 1000 | 1003 |
| 桃花岛主 | 600 | 1004 |
| 水灵 | 150 | 1005 |
| 感动心灵 | 500 | 1006 |
| jack | 10 | 8888 |
+----------+-------+------+
7 rows in set

mysql> #where条件查询
-> #列出book表中3号类别的图书所有信息
-> select * from book where cid=3;
+-----+------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+------------+----------+-----+
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+------------+--------+-------+------------+----------+-----+
3 rows in set

mysql> #列出user表中积分小于500的会员信息
-> #表达式=、 >、<、 >=、<=、！=、<>
-> select * from user where score<500;
+------+----------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
| 1002 | 平平人生 | Lp001@126.com | 13545158219 | 300 |
| 1005 | 水灵 | zs123@371.com | 15823948274 | 150 |
| 8888 | jack | NULL | 11011011000 | 10 |
+------+----------+---------------+-------------+-------+
4 rows in set

mysql> #表达式=、 >、<、 >=、<=、！=、<>
-> #列出book表中价格大于30的图书编号、
#图书名字、和图书价格。
-> select bid,bname,price from book where price>30;
+-----+------------+-------+
| bid | bname | price |
+-----+------------+-------+
| 1 | 中国时代 | 39 |
| 4 | 房间 | 37.6 |
| 5 | 平凡的世界 | 75 |
| 7 | 蜕 | 32 |
+-----+------------+-------+
4 rows in set

mysql> #列出b_order中订购量ordernum为1的用户编号uid
-> #和图书编号bid
-> select uid,bid from b_order where ordernum=1;
+------+-----+
| uid | bid |
+------+-----+
| 1001 | 1 |
| 1001 | 3 |
| 1003 | 3 |
| 1003 | 7 |
| 1005 | 5 |
| 1006 | 5 |
+------+-----+
6 rows in set

mysql> #列出user表中1001和1002会员信息
-> select from user where uid='1001' and uid='1002';
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from user where uid='1001' and uid='1002'' at line 3
mysql> select from user where uid='1001' and uid='1002';
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from user where uid='1001' and uid='1002'' at line 1
mysql> select *
-> from user
-> where uid='1001' and uid='1002';
Empty set

mysql> select *
-> from user
-> where uid='1001' or uid='1002';
+------+----------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
| 1002 | 平平人生 | Lp001@126.com | 13545158219 | 300 |
+------+----------+---------------+-------------+-------+
2 rows in set

mysql> #列出book表中3号类别图书价格大于20元的图书信息
-> select *
-> from book
-> where cid=3 and pirce>20;
1054 - Unknown column 'pirce' in 'where clause'
mysql> select *
-> from book
-> where cid=3 and price>20;
+-----+------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+------------+----------+-----+
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+------------+--------+-------+------------+----------+-----+
3 rows in set

mysql> #列出user表中用户名是‘何“开头的所有信息
-> select *
-> from user
-> where uname like '何%';

+------+--------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+--------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
+------+--------+---------------+-------------+-------+
1 row in set
mysql> select *
-> from user
-> where uname like '何_姑';

+------+--------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+--------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
+------+--------+---------------+-------------+-------+
1 row in set
mysql> #列出user表中没有邮箱email的用户名字、电话
-> select uname,tnum
-> from user
-> where email is null;
+-------+-------------+
| uname | tnum |
+-------+-------------+
| jack | 11011011000 |
+-------+-------------+
1 row in set

mysql> select uname,tnum
-> from user
-> where email is not null;
+----------+-------------+
| uname | tnum |
+----------+-------------+
| 何仙姑 | 13320101991 |
| 平平人生 | 13545158219 |
| 四十不惑 | 18681688818 |
| 桃花岛主 | 13068012987 |
| 水灵 | 15823948274 |
| 感动心灵 | 18612349375 |
+----------+-------------+
6 rows in set

mysql> #列出book中价格在20-30之间图书信息
-> select *
-> from book
-> where price>=20 and price<=30;
+-----+----------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------------+--------+-------+------------+----------+-----+
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
+-----+----------------+--------+-------+------------+----------+-----+
3 rows in set

mysql> select *
-> from book
-> where price between 20 and 30;
+-----+----------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------------+--------+-------+------------+----------+-----+
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
+-----+----------------+--------+-------+------------+----------+-----+
3 rows in set

mysql>

mysql> #别名,字段
-> select bid,bname
-> from book
-> ;
+-----+----------------+
| bid | bname |
+-----+----------------+
| 1 | 中国时代 |
| 2 | 中国历史的屈辱 |
| 3 | 则也要趁早 |
| 4 | 房间 |
| 5 | 平凡的世界 |
| 6 | 心灵鸡汤 |
| 7 | 蜕 |
+-----+----------------+
7 rows in set

mysql> select bid as 图书编号,bname as 图书名字
-> from book
-> ;
+----------+----------------+
| 图书编号 | 图书名字 |
+----------+----------------+
| 1 | 中国时代 |
| 2 | 中国历史的屈辱 |
| 3 | 则也要趁早 |
| 4 | 房间 |
| 5 | 平凡的世界 |
| 6 | 心灵鸡汤 |
| 7 | 蜕 |
+----------+----------------+
7 rows in set

mysql> #将图书表Book中图书按价格作升序排列显示
-> select *
-> from book
-> order by price asc;
+-----+----------------+--------+-------+----------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------------+--------+-------+----------------+----------+-----+
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
+-----+----------------+--------+-------+----------------+----------+-----+
7 rows in set

mysql> #最贵书
-> select *
-> from book
-> order by price desc
-> limit 1;
+-----+------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+------------+----------+-----+
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
+-----+------------+--------+-------+------------+----------+-----+
1 row in set

mysql> select *
-> from book
-> order by price desc;
+-----+----------------+--------+-------+----------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------------+--------+-------+----------------+----------+-----+
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
+-----+----------------+--------+-------+----------------+----------+-----+
7 rows in set

mysql> select *
-> from book
-> order by price desc
-> limit 3;
+-----+------------+--------+-------+----------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+----------------+----------+-----+
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
+-----+------------+--------+-------+----------------+----------+-----+
3 rows in set

mysql> #列出第二贵的书
-> select *
-> from book
-> order by price desc
-> limit 0,1;
+-----+------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+------------+----------+-----+
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
+-----+------------+--------+-------+------------+----------+-----+
1 row in set

mysql> select *
-> from book
-> order by price desc
-> limit 0,2;
+-----+------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+------------+----------+-----+
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
+-----+------------+--------+-------+------------+----------+-----+
2 rows in set

mysql> select *
-> from book
-> order by price desc
-> limit 1,1;
+-----+----------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------+--------+-------+------------+----------+-----+
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
+-----+----------+--------+-------+------------+----------+-----+
1 row in set

mysql> # 列出user表中按积分降序排列结果集中第3-5行数据
-> select *
-> from user
-> order by score desc
-> limit 2,3;
+------+----------+----------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+----------------+-------------+-------+
| 1006 | 感动心灵 | andong@tom.com | 18612349375 | 500 |
| 1002 | 平平人生 | Lp001@126.com | 13545158219 | 300 |
| 1005 | 水灵 | zs123@371.com | 15823948274 | 150 |
+------+----------+----------------+-------------+-------+
3 rows in set

mysql> #查询1001用户的订书编号
-> select bid,uid
-> from b_order
-> where uid='1001';
+-----+------+
| bid | uid |
+-----+------+
| 1 | 1001 |
| 1 | 1001 |
| 3 | 1001 |
+-----+------+
3 rows in set

mysql> select distinct bid,uid
-> from b_order
-> where uid='1001';
+-----+------+
| bid | uid |
+-----+------+
| 1 | 1001 |
| 3 | 1001 |
+-----+------+
2 rows in set

mysql>

mysql> use stuchoose;
Database changed
mysql> select sno 学号,count(*) 门数
-> from elective
-> group by sno;
+----------+------+
| 学号 | 门数 |
+----------+------+
| 10101001 | 4 |
| 10101002 | 5 |
| 10101003 | 3 |
| 10101004 | 4 |
| 10101005 | 2 |
| 10101006 | 3 |
| 10101007 | 1 |
| 10101008 | 2 |
| 10101009 | 4 |
| 10101010 | 1 |
| 10101011 | 1 |
| 10101012 | 1 |
| 10101013 | 1 |
+----------+------+
13 rows in set

mysql> #统计elective表中选课总门数超过3门的学号，门数

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

mysql> #创建数据库
-> create database bookshops0;
Query OK, 1 row affected

mysql>
mysql> #查看数据库
-> show create database bookshops0;
+------------+----------------------------------------------------------------------------------------------+
| Database | Create Database |
+------------+----------------------------------------------------------------------------------------------+
| bookshops0 | CREATE DATABASE `bookshops0` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ |
+------------+----------------------------------------------------------------------------------------------+
1 row in set

mysql>
mysql> #选择数据库
-> use bookshops0;
Database changed
mysql>
mysql> #删除数据库
-> drop database bookshops0;

mysql>
======================================================================================================
mysql>
mysql> #选择数据库
-> use aa;
Database changed
mysql>
mysql> #创建表格
-> create table s0(
-> id int primary key,
-> sname char(6),
-> classes varchar(20)
-> );
Query OK, 0 rows affected

mysql>
mysql> #查看所有数据表
-> show tables;
+-------------+
| Tables_in_aa |
+-------------+
| s0 |
+-------------+
1 row in set

mysql>
mysql> #数据表重命名
-> alter table s0 rename s1;
Query OK, 0 rows affected

mysql>
mysql> #修改字段名
-> alter table s1 change id sid int;

Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> #修改字段类型
-> alter table s1 modify sname varchar(8);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> #修改字段位置
-> alter table s1 modify sid int after sname;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table s1 modify sid int first;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> #新增字段
-> alter table s1 add no int(6);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> #删除字段
-> alter table s1 drop sid;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql> #删除数据表
-> drop table s1;
Query OK, 0 rows affected
======================================================================================================
if exists
======================================================================================================
mysql> #加外键
/*表的关联，ER图中的联系*/
mysql> alter table b0order
mysql>add constraint 外键名 foreign key(引用字段名)
mysql>references 主键表名(引用字段名);
mysql>

mysql> #加复合主键
/*主键约束，表中必有，表示唯一且不为空*/
mysql> alter table b0order
-> add primary key(bid,uid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql>#非空约束
/*必有值，不为空*/not null

mysql>设默认约束（数值直接写，字符用''单引号括起来）
mysql> alter table b0order
-> modify ordernum int default 1;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> alter table book
-> modify pbulisher varchar(50) default '人民邮电';
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> #设置唯一约束
/*字段可为空，但若有数据就不能重复，唯一存在*/
-> alter table user
-> modify tnum varchar(15)
-> unique;
Database changed
Records: 0 Duplicates: 0 Warnings: 0

mysql> use aa;
Database changed
mysql> #创建表格学生表（学号6，姓名4，年龄2，班级）
-> create table student(
-> sno char(6) primary key,
-> sname char(6),
-> age int;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')

create table student(

sno char(6) primary key,

sname char(6),

' at line 3
mysql> create table student(
-> sno char(6) primary key,
-> sname char(6),
-> age int,
-> classes varchar(20)
-> );
Query OK, 0 rows affected

mysql> alter table student
-> modify sname char(10);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student
-> change age sage int(10);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql>
mysql>
mysql>
mysql>
mysql>
mysql> #添加字段
-> alter table student
-> add birth data;
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'data' at line 5
mysql> alter table student
-> add birth date;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student
-> drop birth;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> #调整顺序
-> alter table student
-> modify classes varchar(20) after sname;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student
-> modify sname char(20) first;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table student
-> rename ss;
Query OK, 0 rows affected

mysql> drop table ss;
Query OK, 0 rows affected

mysql>

唯一约束:该字段可以为空，但如果有数据就不能是重复，只能唯一存在。
默认约束:表中字段默认值，如果是数值，直接写;是字符，则单引号括起;
非空约束:该字段必须有值，不能为空。
主键约束:表中必须有的约束，表示唯一且不为空，
一般写primary key，无需再not null。
可以是单字段做主键，也可以是多字段做主键，
primary key(a,b);
外键约束:将表进行关联，即ER图中的联系。
constraint外键名foreign key(字段名）
references主表名（字段名）;
五种约束，可以在创建表时设置，也可以在修改表结构时调整添加。

5种：主键、外键、非空、默认和唯一

======================================================================================================
mysql> #加外键
/*表的关联，ER图中的联系*/
mysql> alter table b0order
mysql>add constraint 外键名 foreign key(引用字段名)
mysql>references 主键表名(引用字段名);
mysql>

mysql> create database bookonline;
Query OK, 1 row affected

mysql> use bookonline;
Database changed

#主键
mysql> create table user(
-> uid char(4) primary key,
-> uname varchar(20),
-> email varchar(20),
-> tnum varchar(15),
-> score int
-> );
Database changed

#非空
mysql> create table book(
-> bid int primary key,
-> bname varchar(50) not null,
-> author char(8),
-> price float,
-> pbulisher varchar(50),
-> discount float,
-> cid int
-> );
Query OK, 0 rows affected

mysql> create table category(
-> cid int primary key,
-> cname varchar(16)
-> );
Query OK, 0 rows affected

#默认，复合主键
mysql> create table order0(
-> bid int,
-> uid char(4),
-> ordernum int default 1,
-> orderdate datetime,
-> deliverydate datetime,
-> primary key(bid,uid)
-> );
Query OK, 0 rows affected

#唯一
mysql> alter table user
-> modify tnum varchar(15) unique;
Database changed
Records: 0 Duplicates: 0 Warnings: 0
mysql>

#外键
mysql> alter table book
-> add constraint c_b_cid foreign key(cid)
-> references category(cid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table order0
-> add constraint u_o_uid foreign key(uid)
-> references user(uid);
Database changed
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table order0
-> add constraint b_o_bid foreign key(bid)
-> references book(bid);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc order0;
+------------+---------+------+------+--------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+------+--------+-------+
| bid | int(11) | NO | PRI | NULL | |
| uid | char(4) | NO | PRI | NULL | |
| ordernum | int(11) | YES | | 1 | |
| orderdate | datetime | YES | | NULL | |
| deliverydate | datetime | YES | | NULL | |
+------------+---------+------+------+--------+-------+
5 rows in set
===================================================
#删除默认
alter table 表名 modify 字段类型；

mysql> alter table order0
-> modify ordernum int;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc order0;
+------------+---------+------+------+--------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+------+--------+-------+
| bid | int(11) | NO | PRI | NULL | |
| uid | char(4) | NO | PRI | NULL | |
| ordernum | int(11) | YES | | NULL | |
| orderdate | datetime | YES | | NULL | |
| deliverydate | datetime | YES | | NULL | |
+------------+---------+------+------+--------+-------+
5 rows in set

#删除非空
alter table 表名 modify 字段类型；

mysql> alter table book
-> modify bname varchar(50);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

#删除唯一
alter table 表名 drop index 字段;

#删除主键
alter table 表名 drop primary key;

mysql> alter table order0
-> drop primary key;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

#删除外键
alter table 表名 drop foreign key 外键名；

mysql> alter table book
-> drop foreign key c_b_cid;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0

修改

mysql> use mydb;
Database changed
mysql> #更新数据，id=4设置code
-> update subscribe
-> set code='LSKDJ'
-> where id=4;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from subscribe;
+----+-------------------+-------+----------+
| id | email | status | code |
+----+-------------------+-------+----------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 0 | JIXDAMI |
| 4 | jimmy123@163.com | 0 | LSKDJ |
| 5 | joy123@163.com | 1 | JSMWNL |
| 6 | zh123@163.com | NULL | NULL |
| 7 | NULL | 0 | NULL |
| 8 | ah123@163.com | 1 | ZXCVB |
| 9 | lo123@163.com | 0 | ASDFG |
| 10 | qin123@163.com | 1 | QWERT |
| 11 | doa123@163.com | NULL | NULL |
| 12 | lilili123@163.com | NULL | NULL |
| 13 | gongo123@163.com | NULL | NULL |
+----+-------------------+-------+----------+
13 rows in set

mysql> #更新整列信息，将status全设置为1
-> update subscribe
-> set status=1;
Query OK, 8 rows affected
Rows matched: 13 Changed: 8 Warnings: 0

mysql> select * from subscribe;
+----+-------------------+-------+----------+
| id | email | status | code |
+----+-------------------+-------+----------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 1 | JIXDAMI |
| 4 | jimmy123@163.com | 1 | LSKDJ |
| 5 | joy123@163.com | 1 | JSMWNL |
| 6 | zh123@163.com | 1 | NULL |
| 7 | NULL | 1 | NULL |
| 8 | ah123@163.com | 1 | ZXCVB |
| 9 | lo123@163.com | 1 | ASDFG |
| 10 | qin123@163.com | 1 | QWERT |
| 11 | doa123@163.com | 1 | NULL |
| 12 | lilili123@163.com | 1 | NULL |
| 13 | gongo123@163.com | 1 | NULL |
+----+-------------------+-------+----------+
13 rows in set

mysql> /*数据更新，两种情况：
01：指定单元格，update 表名 set 字段名=值 where 字段=条件；
02：指定某列，update 表名 set 字段名=值；
*/;
Query OK, 0 rows affected

mysql>

删除

mysql> select * from subscribe1;
+----+-------------------+-------+----------+
| id | email | status | code |
+----+-------------------+-------+----------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 0 | JIXDAMI |
| 4 | jimmy123@163.com | 0 | QKOLPH |
| 5 | joy123@163.com | 1 | JSMWNL |
| 6 | zh123@163.com | NULL | NULL |
| 7 | NULL | 0 | NULL |
| 8 | ah123@163.com | 1 | ZXCVB |
| 9 | lo123@163.com | 0 | ASDFG |
| 10 | qin123@163.com | 1 | QWERT |
| 11 | doa123@163.com | NULL | NULL |
| 12 | lilili123@163.com | NULL | NULL |
| 13 | gongo123@163.com | NULL | NULL |
+----+-------------------+-------+----------+
13 rows in set

mysql> #删除数据，id=12
-> delete from subscribe1
-> where id=12;
Query OK, 1 row affected

mysql> select * from subscribe1;
+----+-------------------+-------+----------+
| id | email | status | code |
+----+-------------------+-------+----------+
| 1 | tom123@163.com | 1 | TRBXPO |
| 2 | lucy123@163.com | 1 | LOICPE |
| 3 | lily123@163.com | 0 | JIXDAMI |
| 4 | jimmy123@163.com | 0 | QKOLPH |
| 5 | joy123@163.com | 1 | JSMWNL |
| 6 | zh123@163.com | NULL | NULL |
| 7 | NULL | 0 | NULL |
| 8 | ah123@163.com | 1 | ZXCVB |
| 9 | lo123@163.com | 0 | ASDFG |
| 10 | qin123@163.com | 1 | QWERT |
| 11 | doa123@163.com | NULL | NULL |
| 13 | gongo123@163.com | NULL | NULL |
+----+-------------------+-------+----------+
12 rows in set

mysql> #删除表中所有数据
-> delete from subscribe2;
Query OK, 13 rows affected

mysql> #删除表
-> drop table subscribe2;
Query OK, 0 rows affected

mysql> /*删除数据，两种情况
01：删除指定行：delete from 表名 where 条件
02：删除所有表中数据 delete from 表名
注意区分delete和drop table*/;
Query OK, 0 rows affected

mysql>

mysql> use bookonline;
Database changed

#查询表中所有数据
mysql> select * from user;
+------+----------+------------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+------------------+-------------+-------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 |
| 1002 | 平平人生 | Lp011@126.com | 13545158219 | 300 |
| 1003 | 四十不惑 | 12345@qq.com | 18688168818 | 1000 |
| 1004 | 桃花岛主 | 810124@qq.com | 13068011234 | 600 |
| 1005 | 水灵 | zs123@371.cn | 15838182503 | 150 |
| 1006 | 感动心灵 | gandong@tom.com | 13641151234 | 500 |
+------+----------+------------------+-------------+-------+
6 rows in set

#查询表中指定列数据
mysql> select uid,uname from user;
+------+----------+
| uid | uname |
+------+----------+
| 1001 | 何大姑 |
| 1002 | 平平人生 |
| 1003 | 四十不惑 |
| 1004 | 桃花岛主 |
| 1005 | 水灵 |
| 1006 | 感动心灵 |
+------+----------+
6 rows in set

#查询符合条件的表中数据
mysql> select * from book where cid=3;
+-----+------------+--------+------+------------+---------+-----+
| bid | bname | author | price | pbulisher | discount | cid |
+-----+------------+--------+------+------------+---------+-----+
| 3 | 择业要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+------------+--------+------+------------+---------+-----+
3 rows in set

# 条件类型6种
#（1）表达式类 =,>,<,>=,<=
# 不等于 !=,<>

mysql> select * from user where score < 500;
+------+----------+----------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+----------------+-------------+-------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 |
| 1002 | 平平人生 | Lp011@126.com | 13545158219 | 300 |
| 1005 | 水灵 | zs123@371.cn | 15838182503 | 150 |
+------+----------+----------------+-------------+-------+
3 rows in set

mysql> select bid,bname,price from book where price > 30;
+-----+------------+------+
| bid | bname | price |
+-----+------------+------+
| 1 | 中国时代 | 39 |
| 4 | 房间 | 37.6 |
| 5 | 平凡的世界 | 75 |
| 7 | 蜕 | 32 |
+-----+------------+------+
4 rows in set

mysql> select uid,bid from bookorder_b_order where ordernum = 1;
+------+-----+
| uid | bid |
+------+-----+
| 1001 | 1 |
| 1001 | 3 |
| 1006 | 5 |
+------+-----+
3 rows in set

#（2）逻辑查询
# A and B （都符合）
# A or B （1个）

mysql> select * from user where uid='1001' or uid ='1002';
+------+----------+----------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+----------------+-------------+-------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 |
| 1002 | 平平人生 | Lp011@126.com | 13545158219 | 300 |
+------+----------+----------------+-------------+-------+
2 rows in set

mysql> select * from book where cid = 3 and price > 20;
+-----+------------+--------+------+------------+---------+-----+
| bid | bname | author | price | pbulisher | discount | cid |
+-----+------------+--------+------+------------+---------+-----+
| 3 | 择业要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+------------+--------+------+------------+---------+-----+
3 rows in set

#（3）模糊查询字段名 like '_'
# _ 一个字符
# % 表示0或多个字符

mysql> select * from user where uname like '何%';
+------+--------+----------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+--------+----------------+-------------+-------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 |
+------+--------+----------------+-------------+-------+
1 row in set

mysql> select * from user where uname like '何_姑';
+------+--------+----------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+--------+----------------+-------------+-------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 |
+------+--------+----------------+-------------+-------+
1 row in set

#（4）空值查询：注意不能用字段名=NULL
# is not null
# is null

mysql> select uname,tnum from user where email is not null;
+----------+-------------+
| uname | tnum |
+----------+-------------+
| 何大姑 | 13320101991 |
| 平平人生 | 13545158219 |
| 四十不惑 | 18688168818 |
| 桃花岛主 | 13068011234 |
| 水灵 | 15838182503 |
| 感动心灵 | 13641151234 |
+----------+-------------+
6 rows in set

#（5）范围查询
# between a and b
# not between a and b
# 注意：可以同<=,>=互换

mysql> select * from book where price not between 20 and 30;
+-----+------------+--------+------+----------------+---------+-----+
| bid | bname | author | price | pbulisher | discount | cid |
+-----+------------+--------+------+----------------+---------+-----+
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+------------+--------+------+----------------+---------+-----+
4 rows in set

mysql> select * from book where price between 20 and 30;
+-----+----------------+--------+------+------------+---------+-----+
| bid | bname | author | price | pbulisher | discount | cid |
+-----+----------------+--------+------+------------+---------+-----+
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 择业要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
+-----+----------------+--------+------+------------+---------+-----+
3 rows in set

mysql> select * from book where price >=20 and price <=30;
+-----+----------------+--------+------+------------+---------+-----+
| bid | bname | author | price | pbulisher | discount | cid |
+-----+----------------+--------+------+------------+---------+-----+
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 择业要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
+-----+----------------+--------+------+------------+---------+-----+
3 rows in set

#（6）列表in 查询
# in(a,b,c,...)
# 注意：可以同or互换

mysql> use xrz31;
Database changed
mysql> select * from book;
+-----+----------------+--------+-------+----------------+----------+-----+
| bid | bname | author | price | publisher | discount | cid |
+-----+----------------+--------+-------+----------------+----------+-----+
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 择业要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+----------------+--------+-------+----------------+----------+-----+
7 rows in set

mysql> select bid,bname from book;
+-----+----------------+
| bid | bname |
+-----+----------------+
| 1 | 中国时代 |
| 2 | 中国历史的屈辱 |
| 3 | 择业要趁早 |
| 4 | 房间 |
| 5 | 平凡的世界 |
| 6 | 心灵鸡汤 |
| 7 | 蜕 |
+-----+----------------+
7 rows in set

mysql> select uid,uname from user;
+------+----------+
| uid | uname |
+------+----------+
| 1001 | 何大姑 |
| 1002 | 平平人生 |
| 1003 | 四十不惑 |
| 1004 | 桃花岛主 |
| 1005 | 水灵 |
| 1006 | 感动心灵 |
+------+----------+
6 rows in set

mysql> select * from user;
+------+----------+-----------------+-------------+-------+---------------+
| uid | uname | email | tnum | score | lianxifangshi |
+------+----------+-----------------+-------------+-------+---------------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 | NULL |
| 1002 | 平平人生 | Lp011@126.com | 13545158219 | 300 | NULL |
| 1003 | 四十不惑 | 12345@qq.com | 18688168818 | 1000 | NULL |
| 1004 | 桃花岛主 | 810124@qq.com | 13068011234 | 600 | NULL |
| 1005 | 水灵 | zs123@371.cn | 15838182503 | 150 | NULL |
| 1006 | 感动心灵 | gandong@tom.com | 13641151234 | 500 | NULL |
+------+----------+-----------------+-------------+-------+---------------+
6 rows in set

mysql> #select * from 名字（查询所有数据）
-> #select 指定字段（uid,uname） from 名字
-> select bname,price from book;
+----------------+-------+
| bname | price |
+----------------+-------+
| 中国时代 | 39 |
| 中国历史的屈辱 | 26 |
| 择业要趁早 | 28 |
| 房间 | 37.6 |
| 平凡的世界 | 75 |
| 心灵鸡汤 | 27 |
| 蜕 | 32 |
+----------------+-------+
7 rows in set

mysql> use xrz31;
Database changed
mysql> select * from user;
> #查询用户表所有的数据
+------+----------+-----------------+-------------+-------+---------------+
| uid | uname | email | tnum | score | lianxifangshi |
+------+----------+-----------------+-------------+-------+---------------+
| 1001 | 何大姑 | Hxg18@163.com | 13320101991 | 20 | NULL |
| 1002 | 平平人生 | Lp011@126.com | 13545158219 | 300 | NULL |
| 1003 | 四十不惑 | 12345@qq.com | 18688168818 | 1000 | NULL |
| 1004 | 桃花岛主 | 810124@qq.com | 13068011234 | 600 | NULL |
| 1005 | 水灵 | zs123@371.cn | 15838182503 | 150 | NULL |
| 1006 | 感动心灵 | gandong@tom.com | 13641151234 | 500 | NULL |
+------+----------+-----------------+-------------+-------+---------------+
6 rows in set

mysql> select uid,uname,score from user;
> #查询用户表中的用户编号，用户名，用户积分
+------+----------+-------+
| uid | uname | score |
+------+----------+-------+
| 1001 | 何大姑 | 20 |
| 1002 | 平平人生 | 300 |
| 1003 | 四十不惑 | 1000 |
| 1004 | 桃花岛主 | 600 |
| 1005 | 水灵 | 150 |
| 1006 | 感动心灵 | 500 |
+------+----------+-------+
6 rows in set

mysql> select uname,score,uid from user;
> #查询用户表中的用户名，用户积分，用户编号
> #指定列查询
+----------+-------+------+
| uname | score | uid |
+----------+-------+------+
| 何大姑 | 20 | 1001 |
| 平平人生 | 300 | 1002 |
| 四十不惑 | 1000 | 1003 |
| 桃花岛主 | 600 | 1004 |
| 水灵 | 150 | 1005 |
| 感动心灵 | 500 | 1006 |
+----------+-------+------+
6 rows in set

mysql> #where条件查询
-> #列出book表中3号类别的图书所有信息
->where条件查询，一共6种
->select *|列名 [，列名]
-> from 表名
->where 条件;
-> select * from book where cid=3;
+-----+------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+------------+--------+-------+------------+----------+-----+
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
+-----+------------+--------+-------+------------+----------+-----+
3 rows in set

mysql> #列出user表中积分小于500的会员信息
-> #1.表达式=、 >、<、 >=、<=、！=、<>
-> select * from user where score<500;
+------+----------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
| 1002 | 平平人生 | Lp001@126.com | 13545158219 | 300 |
| 1005 | 水灵 | zs123@371.com | 15823948274 | 150 |
| 8888 | jack | NULL | 11011011000 | 10 |
+------+----------+---------------+-------------+-------+
4 rows in set

mysql> #逻辑查询 A and B (AB条件都满足)、
A or B（AB条件满足一个即可
->select *
-> from user
-> where uid='1001' and uid='1002';
Empty set

mysql> #列出user表中用户名是‘何“开头的所有信息
->模糊查询字段名 like '王%' _ 1个字符
%表示0或多个字符
->select *
-> from user
-> where uname like '何%';

+------+--------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+--------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
+------+--------+---------------+-------------+-------+
1 row in set
mysql> select *
-> from user
-> where uname like '何_姑';

+------+--------+---------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+--------+---------------+-------------+-------+
| 1001 | 何仙姑 | Hxg18@163.com | 13320101991 | 20 |
+------+--------+---------------+-------------+-------+
1 row in set
mysql> #列出user表中没有邮箱email的用户名字、电话
->#空值查询 null 、not null
#select...from...
#where 字段名 is null;
-> select uname,tnum
-> from user
-> where email is null;
+-------+-------------+
| uname | tnum |
+-------+-------------+
| jack | 11011011000 |
+-------+-------------+
1 row in set

mysql> 范围查询 between ..x.and...y
not between...and...
注意：可以与>= <=互换，包括x,y
->select *
-> from book
-> where price between 20 and 30;
+-----+----------------+--------+-------+------------+----------+-----+
| bid | bname | author | price | pubisher | discount | cid |
+-----+----------------+--------+-------+------------+----------+-----+
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 3 | 则也要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
+-----+----------------+--------+-------+------------+----------+-----+
3 rows in set

#select 字段名 as 别名 from ....
#或
#select 字段名别名 from ....
mysql> select uname as username,tnum as telephone from user where score <200;
+----------+-------------+
| username | telephone |
+----------+-------------+
| 何大姑 | 13320101991 |
| 水灵 | 15838182503 |
| jack | 110 |
+----------+-------------+
3 rows in set

mysql> select * from b_order where orderdate like '2016-10-%';
+-----+------+----------+-------------------+------------+
| bid | uid | ordernum | orderdate | deliverydate |
+-----+------+----------+-------------------+------------+
| 1 | 1006 | 2 | 2016-10-21 00:00:00 | NULL |
| 3 | 1003 | 10 | 2016-10-01 00:00:00 | NULL |
+-----+------+----------+-------------------+------------+
2 rows in set

#排序查询(不写asc desc 默认升序)
#升序 select ... from ... order by 字段名 asc;
#降序 select ... from ... order by 字段名 desc;
mysql> select bname,price from book order by price asc;
+----------------+------+
| bname | price |
+----------------+------+
| 中国历史的屈辱 | 26 |
| 心灵鸡汤 | 27 |
| 择业要趁早 | 28 |
| 蜕 | 32 |
| 房间 | 37.6 |
| 中国时代 | 39 |
| 平凡的世界 | 75 |
+----------------+------+
7 rows in set

mysql> select bname,price from book order by price desc;
+----------------+------+
| bname | price |
+----------------+------+
| 平凡的世界 | 75 |
| 中国时代 | 39 |
| 房间 | 37.6 |
| 蜕 | 32 |
| 择业要趁早 | 28 |
| 心灵鸡汤 | 27 |
| 中国历史的屈辱 | 26 |
+----------------+------+
7 rows in set

#排序条件多个 (逗号间隔)
#select ... from ... order by 字段名 ...,字段名 ...;
mysql> select * from book order by cid asc,price desc;
+-----+----------------+--------+------+----------------+---------+-----+
| bid | bname | author | price | pbulisher | discount | cid |
+-----+----------------+--------+------+----------------+---------+-----+
| 1 | 中国时代 | 师永刚 | 39 | 作家出版社 | 27.8 | 1 |
| 2 | 中国历史的屈辱 | 王重旭 | 26 | 华夏出版社 | 18.2 | 2 |
| 7 | 蜕 | 赵婷 | 32 | 上海出版社 | 28.5 | 3 |
| 3 | 择业要趁早 | 海文 | 28 | 海天出版社 | 19.3 | 3 |
| 6 | 心灵鸡汤 | 关然 | 27 | 大豫出版社 | 20 | 3 |
| 5 | 平凡的世界 | 路遥 | 75 | 北京出版社 | 63.75 | 4 |
| 4 | 房间 | 爱玛 | 37.6 | 人民文学出版社 | 26.3 | 4 |
+-----+----------------+--------+------+----------------+---------+-----+
7 rows in set

#条件+排序，先写条件
#select ... from ... where ... order by ... asc|desc;
mysql> select bname,price from book where cid = 3 order by price desc;
+------------+------+
| bname | price |
+------------+------+
| 蜕 | 32 |
| 择业要趁早 | 28 |
| 心灵鸡汤 | 27 |
+------------+------+
3 rows in set

#列出user表中按积分降序排列结果集中的第3-5行数据
mysql> select * from user order by score desc limit 2,3;
+------+----------+------------------+-------------+-------+
| uid | uname | email | tnum | score |
+------+----------+------------------+-------------+-------+
| 1006 | 感动心灵 | gandong@tom.com | 13641151234 | 500 |
| 1002 | 平平人生 | Lp011@126.com | 13545158219 | 300 |
| 1005 | 水灵 | zs123@371.cn | 15838182503 | 150 |
+------+----------+------------------+-------------+-------+
3 rows in set

#去除重复行
#查询1001用户的订书编号
#select distinct ... from ... where ...;
mysql> select bid,uid from b_order where uid = '1001';
+-----+------+
| bid | uid |
+-----+------+
| 1 | 1001 |
| 1 | 1001 |
| 3 | 1001 |
+-----+------+
3 rows in set

mysql> select distinct bid,uid from b_order where uid = '1001';
+-----+------+
| bid | uid |
+-----+------+
| 1 | 1001 |
| 3 | 1001 |
+-----+------+
2 rows in set

#(2)查询会员表,输出积分低于200的会员的昵称和联系电话，并且分别用英文 username,telephone指定别名
mysql> select uname as username,tnum as telephone from user where score <200;
+----------+-------------+
| username | telephone |
+----------+-------------+
| 何大姑 | 13320101991 |
| 水灵 | 15838182503 |
| jack | 110 |
+----------+-------------+
3 rows in set

#(4)查询订购表,输出订购日期是2016年10月的订单的详细信息
mysql> select * from b_order where orderdate like '2016-10-%';
+-----+------+----------+-------------------+------------+
| bid | uid | ordernum | orderdate | deliverydate |
+-----+------+----------+-------------------+------------+
| 1 | 1006 | 2 | 2016-10-21 00:00:00 | NULL |
| 3 | 1003 | 10 | 2016-10-01 00:00:00 | NULL |
+-----+------+----------+-------------------+------------+
2 rows in set

#(5)查询订购表,输出订货的会员的编号,要求删除重复行
mysql> select distinct uid from b_order;
+------+
| uid |
+------+
| 1001 |
| 1003 |
| 1005 |
| 1006 |
+------+
4 rows in set

#(6)查询图书表,输出图书的名称和价格,并把查询结果按价格降序排列
mysql> select bname,price from book order by price desc;
+----------------+------+
| bname | price |
+----------------+------+
| 平凡的世界 | 75 |
| 中国时代 | 39 |
| 房间 | 37.6 |
| 蜕 | 32 |
| 择业要趁早 | 28 |
| 心灵鸡汤 | 27 |
| 中国历史的屈辱 | 26 |
+----------------+------+
7 rows in set

#(7)查询图书表,输出价格最高的三种图书的名称和价格
mysql> select bname,price from book order by price desc limit 3;
+------------+------+
| bname | price |
+------------+------+
| 平凡的世界 | 75 |
| 中国时代 | 39 |
| 房间 | 37.6 |
+------------+------+
3 rows in set

#限制输入
#枚举类型（单选）
#enum('男','女','保密')
#多选
#set('运动','唱歌','跳舞','戏剧','手工','其他')

根据以下的要求完成学生表（student）SQL语句的创建。
student(id,name,tel,gender,hobby,time)
学生姓名name：可以使用中文，不允许重复，长度在2~20个字符之间。
手机号码tel：长度为11个字符。
性别gender：有男、女、保密3种选择。
爱好hobby：指定用户可以多选的项，“运动、唱歌、跳舞、戏剧、手工、其他”。
入学时间time：注册时的日期和时间。（用datetime）

mysql> create table student (
-> id int primary key comment '用户id',
-> name varchar(20) unique not null comment '学生姓名',
-> tel int(11) not null comment '手机号码',
-> gender enum('男','女','保密') not null comment '性别',
-> hobby set('运动','唱歌','跳舞','戏剧','手工','其他') not null comment '爱好',
-> time timestamp comment '入学时间'
-> );
Query OK, 0 rows affected

mysql> #分组查询
-> #统计每类图书数量
#select 输出的字段 from 表 group by 按此字段统计分组;
#count(*) 计数函数；（*）：所有列；（字段）：该字段的

mysql> select cid,count(*) from book group by cid;
+-----+---------+
| cid | count(*) |
+-----+---------+
| 1 | 1 |
| 2 | 1 |
| 3 | 3 |
| 4 | 2 |
+-----+---------+
4 rows in set

#列出每类图书的最高价
#select 输出的字段 from 表 group by 按此字段统计分组;
#max(price) 输出该字段的最大值

mysql> select cid,max(price) from book group by cid;
+-----+-----------+
| cid | max(price) |
+-----+-----------+
| 1 | 39 |
| 2 | 26 |
| 3 | 32 |
| 4 | 75 |
+-----+-----------+
4 rows in set

#函数：统计，最大值，最小值，平均值，求和
mysql> select cid,count(*),max(price),min(price),avg(price),sum(price) from book group by cid;
+-----+---------+-----------+--------------------+-------------------+------------------+
| cid | count(*) | max(price) | min(price) | avg(price) | sum(price) |
+-----+---------+-----------+--------------------+-------------------+------------------+
| 1 | 1 | 39 | 39 | 39 | 39 |
| 2 | 1 | 26 | 26 | 26 | 26 |
| 3 | 3 | 32 | 27 | 29 | 87 |
| 4 | 2 | 75 | 37.599998474121094 | 56.29999923706055 | 112.599998474121 |
+-----+---------+-----------+--------------------+-------------------+------------------+
4 rows in set

#1.每个学生选课门数
mysql> select sno as 学生,count(*) as 选课数 from elective group by sno;

#2.每门课程的选课人数
mysql> select cno as 课程,count(*) as 选课人数 from elective group by cno;

#3.统计每门课的最高分、最低分、平均分。
mysql> select cno as 课程,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分 from elective group by cno;

#统计男女人数
mysql> select sgender as 性别,count(*) as 人数 from studentinfo group by sgender;
#多条件分组（嵌套）
mysql> select sclass as 班级,sgender as 性别,count(*) as 人数 from studentinfo group by sclass,sgender;

#查询框架
select * 或列名,列名或函数或表达式
from 表名
where 条件（不能写函数）
group by 列名 having 条件（可以写函数）
order by 列名 asc|desc
limit m[,n] （m,n 是数字）

注意：having 条件与 where 条件区别：
1.语法中位置不同，先where
2.执行顺序：先 where 再分组；having只能跟在分组结果集上筛选数据
3.统计函数不能写在 where 之后。

=============================================================================
#统计每班的人数
#select sclass as 班级,count(*) as 人数 from studentinfo group by sclass;

###统计每个班的男生女生人数
mysql> select sclass as 班级,sgender as 性别,count(*) as 人数 from studentinfo group by sclass,sgender;

#统计男女人数
mysql> select sgender as 性别,count(*) as 人数 from studentinfo group by sgender;

###统计"电子商务101"男女人数
mysql> select sgender as 性别,count(*) as 人数 from studentinfo where sclass="电子商务101" group by sgender;

#1.每个学生选课门数
mysql> select sno as 学生,count(*) as 选课数 from elective group by sno;

###elective统计表中选课总门数超过3门的学号
mysql> select sno as 学生,count(*) as 选课数 from elective group by sno having count(*)>=3;

#2.每门课程的选课人数
mysql> select cno as 课程,count(*) as 选课人数 from elective group by cno;

#3.统计每门课的最高分、最低分、平均分。
mysql> select cno as 课程,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分 from elective group by cno;

###4.统计每个学生的最高分、最低分、平均分。(只显示平均分不低于70分)
mysql> select sno as 学生,max(score) as 最高分,min(score) as 最低分,avg(score) as 平均分 from elective group by sno having avg(score)>=70;

#请列出 book 中价格最高的书的信息
mysql> select * from book order by price desc limit 1;

=============================================================================
（1）查询图书表，输出所有图书的最高价格，最低价格，平均价格

mysql> select max(price) 最高价格,min(price) 最低价格,avg(price) 平均价格 from book;

（2）查询图书表，输出每一类图书的数量

mysql> select cid 类别,count(*) 数量 from book group by cid;

（3）查询图书表，输出每一类图书的最高价格，最低价格，平均价格

mysql> select cid 类别,max(price) 最高价格,min(price) 最低价格,avg(price) 平均价格 from book group by cid;

（4）查询订购表，输出订购超过3本的会员的编号和订购数量

mysql> select uid 会员编号,sum(ordernum) 总数 from b_order group by uid having sum(ordernum)>3;

1.多表连接查询
(1) select ... from 表1,表2 where 表1.字段=表2.字段

列出学生姓名，课程编号，成绩
mysql> select sname,cno,score from studentinfo,elective where studentinfo.sno=elective.sno;
列出学生学号，姓名，课程编号，成绩 (输出指定唯一列名)
mysql> select studentinfo.sno,sname,cno,score from studentinfo,elective where studentinfo.sno=elective.sno;
列出课程名，学号，成绩
mysql> select cname,sno,score from course,elective where course.cno=elective.cno;
列出课程编号，课程名，学号，成绩
mysql> select course.cno,cname,sno,score from course,elective where course.cno=elective.cno;

(2) join ... on
select form 表A join 表B on 表A.字段=表B.字段
where
group by ... having
order by
limt;

内连接 inner join ... on
外连接 outer join ... on
左 left join ... on
右 right join ... on
交叉连接 cross （基本不用）

连接studentinfo，elective表
mysql> select * from studentinfo s,elective e where s.sno = e.sno;
连接course，elective表（内连接）
mysql> select * from course join elective on course.cno = elective.cno;

列出教师姓名，教授课程编号
mysql> select tname,cno from teacher t join course c on t.tno = c.ctno;
列出每位学生姓名，选课门数
mysql> select sname,count(cno) from studentinfo s join elective e on s.sno = e.sno group by sname;
统计每位老师的授课数量，列出教师编号，教师名字和授课数量
mysql> select t.tno,tname,count(cno) from teacher t join course c on t.tno = c.ctno group by t.tno;
查出10101001学生的姓名，选修课程的平均分
mysql> select sname,avg(score) from studentinfo s join elective e on s.sno = e.sno group by e.sno having e.sno="10101001";

查询学生选课数据库，输出考试不及格的学生的学号，姓名，课号，成绩
select sno,sname,cno,score from studentinfo s inner join elective e on s.sno = e.sno where score < 60;

查询学生选课数据库，输出考试不及格的学生的学号，姓名，课名，成绩
select sno,sname,cname,score from studentinfo s join elective e on s.sno = e.sno join course c on c.cno = e.cno where score < 60;
select sno,sname,cname,score from studentinfo s,elective e,course c where s.sno = e.sno and c.cno = e.cno and score < 60;

三+多表连接
select
from 表A join 表B on 表A.字段=表B.字段
join 表C on 表B.字段=表C.字段;

注意：join...on 多表内连接，找好连接条件；（2个，3个或更多表）但很多表连接则启用视图

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

左外连接left join...on

查看每个学生的选课情况，未选课的也列出
select * from studentinfo s left join elective e on e.sno = s.sno;

查看学号，姓名，课号，学分，未选课的也列出
select s.sno,sname,cno,score from studentinfo s left join elective e on e.sno = s.sno;

查看每门课程的老师，为开设课程的老师也要列出
select * from teacher t left join course c on c.ctno=t.tno;

注意：外连接仅支持两个表，不支持多表
=================================================================================

右外连接right join...on

查看未被选的课程。
select * from elective e right join course c on e.cno=c.cno;

查看未被选的课程的编号，名称。
select c.cno,cname from elective e right join course c on e.cno=c.cno where sno is null;

注意：外连接仅支持两个表，不支持多表

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

create table S(
SNO char(4) primary key,
SNAME varchar(20),
AGE int(5),
SEX char(4)
);

create table SC(
SNO char(4),
CNO char(4),
GRADE int(5),
primary key(SNO,CNO)
);

create table C(
CNO char(4) primary key,
CNAME varchar(20),
TEACHER varchar(20)
);

1、对于教学数据库的三个基本表：
S（S#，SNAME，AGE，SEX）
SC（S#，C#，GRADE）
C（C#，CNAME，TEACHER）
试用SQL的查询语句表达下列查询：
1）检索“程军”老师所授课程的课程号和课程名。

select C#,CNAME from C where TEACHER="程军";

2）检索年龄大于21的男学生学号和姓名。

select S#,SNAME from S where AGE>21 and SEX="男";

3）检索“李强”同学不学课程的课程号。

select C# from S join SC on S.S# = SC.S# where SNAME= "李强" and C# is not null;

SELECT CNO FROM C WHERE CNO NOT IN
(SELECT CNO FROM SC,S WHERE SC.SNO=S.SNO AND S.SNAME='李强')

4）检索至少选修两门课程的学生学号。

select S# from SC group by S# having count(S#)>=2;

5）检索所有姓刘的学生的平均年龄。

select avg(AGE) from S where SNAME like "刘%";

6）检索选修课程名为“C语言”的学生学号和姓名。

select S.S#,SNAME from S join SC on S.S# = SC.S# join C on C.C# = SC.C# where CNAME = "C语言";

7）检索选修课程号为K5的学生学号和姓名。

select S.S#,SNAME from S join SC on S.S# = SC.S# where C# = "K5";

8）在SC表中插入一条新记录（’S5’，’C1’，98）。

insert into SC values ("S5","C1",98);

9）查询选修了课程但没有成绩的学生学号及课程号。

select S.S#,C# from S left join SC on S.S# = SC.S# where C# is not null and GRADE is null;

10)把低于总平均成绩的男生的成绩提高10分。

select 读者号,姓名 from 读者 where 工作单位 = (select 工作单位 from 读者 where 姓名 = "张三");
select a2.读者号,a2.姓名 from 读者 a1 join 读者 a2 on a1.工作单位=a2.工作单位 where a1.姓名 = "张三";

====================================================================
子查询
（= > < 连接的子查询）
select * from 借阅 where 读者号 = (select 读者号 from 读者 where 姓名 = "李红");
select * from 图书 where 单价 > (select 单价 from 图书 where 书名 = "平凡的世界")；
注意：子查询是一个值

=================================================================
子查询
（in 连接的子查询）
select 图书号,借期,还期 from 借阅 where 读者号 in (select 读者号 from 读者 where 姓名 like "王%");
注意：子查询结果集有多行记录

=====================================================================
批量比较子查询
子查询结果不止一个，父查询和子查询用比较运算符连接子查询前加all或any
all 全部
any 一次

================================================================
补考：
select sname from studentinfo where sno = any(select sno from elective where score < 60);
不补考：
select sname from studentinfo where sno != all(select sno from elective where score < 60);

================================================================
子查询
exists 存在
select tname,tpro from teacher where not exists(select * from teacher where tpro = "教授");

=================================================================
子查询
嵌套一个 select

外层：
select
insert
update
delete

====================================================================
====================================================================
====================================================================
====================================================================
两表内联（ , ... where ）（ join ... on ）
子查询( select ... in/any/all/exists )

①②③④⑤⑥⑦
====================================================================
====================================================================
(1)输出所有图书的图书名称、价格以及所属类别名称

①mysql> select bname,price,cname from book b join category c on c.cid = b.cid;
+----------------+------+--------+
| bname | price | cname |
+----------------+------+--------+
| 中国时代 | 39 | 历史 |
| 中国历史的屈辱 | 26 | 家教 |
| 择业要趁早 | 28 | 文化 |
| 心灵鸡汤 | 27 | 文化 |
| 蜕 | 32 | 文化 |
| 房间 | 37.6 | 小说 |
| 平凡的世界 | 75 | 小说 |
+----------------+------+--------+
7 rows in set

②mysql> select bname,price,cname from book b,category c where c.cid = b.cid;
+----------------+------+--------+
| bname | price | cname |
+----------------+------+--------+
| 中国时代 | 39 | 历史 |
| 中国历史的屈辱 | 26 | 家教 |
| 择业要趁早 | 28 | 文化 |
| 心灵鸡汤 | 27 | 文化 |
| 蜕 | 32 | 文化 |
| 房间 | 37.6 | 小说 |
| 平凡的世界 | 75 | 小说 |
+----------------+------+--------+
7 rows in set

(2)输出订购了“平凡的世界”的会员昵称、联系电话、订购量

①mysql> select uname,tnum,ordernum from user u join b_order bo on u.uid = bo.uid where bid = (select bid from book where bname = "平凡的世界");
+----------+-------------+----------+
| uname | tnum | ordernum |
+----------+-------------+----------+
| 水灵 | 15838182503 | 1 |
| 感动心灵 | 13641151234 | 1 |
+----------+-------------+----------+
2 rows in set

②mysql> select uname,tnum,ordernum from user u join b_order bo on u.uid = bo.uid join book b on b.bid = bo.bid where bname = "平凡的世界";
+----------+-------------+----------+
| uname | tnum | ordernum |
+----------+-------------+----------+
| 水灵 | 15838182503 | 1 |
| 感动心灵 | 13641151234 | 1 |
+----------+-------------+----------+
2 rows in set

(3）输出订购了图书的会员的昵称和联系电话

①mysql> select distinct uname,tnum from user u left join b_order bo on u.uid = bo.uid where ordernum is not null;
+----------+-------------+
| uname | tnum |
+----------+-------------+
| 何仙姑 | 13320101991 |
| 感动心灵 | 13641151234 |
| 四十不惑 | 18688168818 |
| 水灵 | 15838182503 |
+----------+-------------+
4 rows in set

②mysql> select uname,tnum from user where uid in (select distinct uid from b_order);
+----------+-------------+
| uname | tnum |
+----------+-------------+
| 何仙姑 | 13320101991 |
| 四十不惑 | 18688168818 |
| 水灵 | 15838182503 |
| 感动心灵 | 13641151234 |
+----------+-------------+
4 rows in set

③mysql> select uname,tnum from user where uid = any(select distinct uid from b_order);
+----------+-------------+
| uname | tnum |
+----------+-------------+
| 何仙姑 | 13320101991 |
| 四十不惑 | 18688168818 |
| 水灵 | 15838182503 |
| 感动心灵 | 13641151234 |
+----------+-------------+
4 rows in set

(4）输出没人订购的图书的名称和价格

①mysql> select bname,price from book b left join b_order bo on b.bid = bo.bid where ordernum is null;
+----------------+------+
| bname | price |
+----------------+------+
| 中国历史的屈辱 | 26 |
| 房间 | 37.6 |
| 心灵鸡汤 | 27 |
+----------------+------+
3 rows in set

②mysql> select bname,price from book where bid not in (select distinct bid from b_order);
+----------------+------+
| bname | price |
+----------------+------+
| 中国历史的屈辱 | 26 |
| 房间 | 37.6 |
| 心灵鸡汤 | 27 |
+----------------+------+
3 rows in set

③mysql> select bname,price from book where bid != all(select distinct bid from b_order);
+----------------+------+
| bname | price |
+----------------+------+
| 中国历史的屈辱 | 26 |
| 房间 | 37.6 |
| 心灵鸡汤 | 27 |
+----------------+------+
3 rows in set

(5）输出详细订购信息,包括订购图书的会员呢称,联系电话,订图书名称,数量，价格，折扣价

①mysql> select uname,tnum,bname,ordernum,price,discount from user u join b_order bo on u.uid = bo.uid left join book b on b.bid = bo.bid;
+----------+-------------+------------+----------+------+---------+
| uname | tnum | bname | ordernum | price | discount |
+----------+-------------+------------+----------+------+---------+
| 何仙姑 | 13320101991 | 中国时代 | 2 | 39 | 27.8 |
| 何仙姑 | 13320101991 | 中国时代 | 1 | 39 | 27.8 |
| 感动心灵 | 13641151234 | 中国时代 | 2 | 39 | 27.8 |
| 何仙姑 | 13320101991 | 择业要趁早 | 1 | 28 | 19.3 |
| 四十不惑 | 18688168818 | 择业要趁早 | 1 | 28 | 19.3 |
| 水灵 | 15838182503 | 平凡的世界 | 1 | 75 | 63.75 |
| 感动心灵 | 13641151234 | 平凡的世界 | 1 | 75 | 63.75 |
| 四十不惑 | 18688168818 | 蜕 | 1 | 32 | 28.5 |
| 水灵 | 15838182503 | 蜕 | 3 | 32 | 28.5 |
| 感动心灵 | 13641151234 | 蜕 | 2 | 32 | 28.5 |
+----------+-------------+------------+----------+------+---------+
10 rows in set

②mysql> select uname,tnum,bname,ordernum,price,discount from user u,b_order bo,book b where u.uid = bo.uid and b.bid = bo.bid;
+----------+-------------+------------+----------+------+---------+
| uname | tnum | bname | ordernum | price | discount |
+----------+-------------+------------+----------+------+---------+
| 何仙姑 | 13320101991 | 中国时代 | 2 | 39 | 27.8 |
| 何仙姑 | 13320101991 | 中国时代 | 1 | 39 | 27.8 |
| 感动心灵 | 13641151234 | 中国时代 | 2 | 39 | 27.8 |
| 何仙姑 | 13320101991 | 择业要趁早 | 1 | 28 | 19.3 |
| 四十不惑 | 18688168818 | 择业要趁早 | 1 | 28 | 19.3 |
| 水灵 | 15838182503 | 平凡的世界 | 1 | 75 | 63.75 |
| 感动心灵 | 13641151234 | 平凡的世界 | 1 | 75 | 63.75 |
| 四十不惑 | 18688168818 | 蜕 | 1 | 32 | 28.5 |
| 水灵 | 15838182503 | 蜕 | 3 | 32 | 28.5 |
| 感动心灵 | 13641151234 | 蜕 | 2 | 32 | 28.5 |
+----------+-------------+------------+----------+------+---------+
10 rows in set

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

DDL 数据定义语言
@#@视图创建

create view 视图名(字段别名,字段别名) as select 字段 from 表名;

create nopass_view(学号,姓名,课程名,成绩) as select sno,sname,cname,score from ...;
===========================================================
查看视图

视图结构
describe 视图名;
@#@简写：
desc 视图名;

视图定义信息（状态信息）
show table status like "视图名"\G

@#@视图创建信息
show create view 视图名;

===========================================================
修改视图
@#@(1) create or replace view 视图名 as select ...from;

@#@(2) alter view 视图名 as select from;
===========================================================
@#@删除视图
drop view if exists 视图名;
===========================================================
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
===========================================================
DQL 查询
DML 修改

插入数据
insert into 视图名 values ('s','s',1,'s');

update 视图名 set 字段名 = XX where ...;

delete from 视图名 where ...;

select 字段 from 视图名 where ...;
===========================================================

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

会员力量，点亮园子希望

刷新页面返回顶部