MySQL SQL介绍(1)
一、MySQL SQL介绍
1.1.1 SQL 应用基础
- 常用的列属性约束
1、primary key （主键）
2、unique （唯一）
3、not null （不为空）
4、default （默认值）
5、auto_increment （自增长）
6、unsigned （无符号） 常与zerofill（零填充）配合
7、comment （注释）
- 创库建表插入数据回顾
#创建school
mysql> create database school character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> Show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
| cp850_bin | cp850 | 80 | | Yes | 1 |
| hp8_english_ci | hp8 | 6 | Yes | Yes | 1 |
| hp8_bin | hp8 | 72 | | Yes | 1 |
| koi8r_general_ci | koi8r | 7 | Yes | Yes | 1 |
mysql> use school
Database changed
#创建测试环境表
#学生表
mysql> create table student(
-> sno int not null primary key auto_increment comment '学号',
-> sname varchar(255) not null comment '学生姓名',
-> sage tinyint(3) unsigned zerofill not null comment '学生年龄',
-> ssex char(1) not null comment '学生性别'
-> )engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.00 sec)
mysql> desc student;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+----------------+
| sno | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| sage | tinyint(3) unsigned zerofill | NO | | NULL | |
| ssex | char(1) | NO | | NULL | |
+-------+------------------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
#教师表
mysql> create table teacher(
-> tno int not null primary key auto_increment comment '教师编号',
-> tname varchar(255) not null comment '教师名字'
-> )engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.04 sec)
mysql> desc teacher;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| tno | int(11) | NO | PRI | NULL | auto_increment |
| tname | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
#课程表
mysql> create table course(
-> cno int not null unique primary key auto_increment comment '课程编号',
-> cname varchar(255) unique not null comment '课程名称',
-> tno int unique not null comment '教师编号'
-> )engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.04 sec)
mysql> desc course;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| cno | int(11) | NO | PRI | NULL | auto_increment |
| cname | varchar(255) | NO | UNI | NULL | |
| tno | int(11) | NO | UNI | NULL | |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#成绩表
mysql> create table score(
-> sno int not null comment '学号',
-> cno int not null comment '课程编号',
-> score tinyint(3) unsigned zerofill not null comment '学生成绩'
-> )engine=innodb charset=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql> desc score;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+----------------+
| sno | int(11) | NO | | NULL | auto_increment |
| cno | int(11) | NO | | NULL | |
| score | tinyint(3) unsigned zerofill | NO | | NULL | |
+-------+------------------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#学生表插入数据
mysql> INSERT INTO student(sno,sname,sage,ssex)
-> VALUES
-> (1,'zhang3',18,'m'),
-> (2,'zhang4',18,'m'),
-> (3,'li4',18,'m'),
-> (4,'wang5',19,'f'),
-> (5,'zh4',18,'m'),
-> (6,'zhao4',18,'m'),
-> (7,'ma6',19,'f');
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select sno,sname,sage,ssex from student;
+-----+--------+------+------+
| sno | sname | sage | ssex |
+-----+--------+------+------+
| 1 | zhang3 | 018 | m |
| 2 | zhang4 | 018 | m |
| 3 | li4 | 018 | m |
| 4 | wang5 | 019 | f |
| 5 | zh4 | 018 | m |
| 6 | zhao4 | 018 | m |
| 7 | ma6 | 019 | f |
+-----+--------+------+------+
7 rows in set (0.00 sec)
mysql> INSERT INTO student(sname,sage,ssex)
-> VALUES
-> ('oldboy',20,'m'),
-> ('oldgirl',20,'f'),
-> ('oldp',25,'m');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select sno,sname,sage,ssex from student;
+-----+---------+------+------+
| sno | sname | sage | ssex |
+-----+---------+------+------+
| 1 | zhang3 | 018 | m |
| 2 | zhang4 | 018 | m |
| 3 | li4 | 018 | m |
| 4 | wang5 | 019 | f |
| 5 | zh4 | 018 | m |
| 6 | zhao4 | 018 | m |
| 7 | ma6 | 019 | f |
| 8 | oldboy | 020 | m |
| 9 | oldgirl | 020 | f |
| 10 | oldp | 025 | m |
+-----+---------+------+------+
10 rows in set (0.00 sec)
#教师表插入数据
mysql> INSERT INTO teacher(tno,tname) VALUES
-> (101,'oldboy'),
-> (102,'hesw'),
-> (103,'oldguo');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select tno,tname from teacher;
+-----+--------+
| tno | tname |
+-----+--------+
| 101 | oldboy |
| 102 | hesw |
| 103 | oldguo |
+-----+--------+
3 rows in set (0.00 sec)
#课程表插入数据
mysql> INSERT INTO course(cno,cname,tno)
-> VALUES
-> (1001,'linux',101),
-> (1002,'python',102),
-> (1003,'mysql',103);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select cno,cname,tno from course;
+------+--------+-----+
| cno | cname | tno |
+------+--------+-----+
| 1001 | linux | 101 |
| 1002 | python | 102 |
| 1003 | mysql | 103 |
+------+--------+-----+
3 rows in set (0.00 sec)
#成绩表插入数据
mysql> INSERT INTO score(sno,cno,score)
-> VALUES
-> (1,1001,80),
-> (1,1002,59),
-> (2,1002,90),
-> (2,1003,100),
-> (3,1001,99),
-> (3,1003,40),
-> (4,1001,79),
-> (4,1002,61),
-> (4,1003,99),
-> (5,1003,40),
-> (6,1001,89),
-> (6,1003,77),
-> (7,1001,67),
-> (7,1003,82),
-> (8,1001,70),
-> (9,1003,80),
-> (10,1003,96);
Query OK, 17 rows affected (0.01 sec)
Records: 17 Duplicates: 0 Warnings: 0
mysql> select sno,cno,score from score;
+-----+------+-------+
| sno | cno | score |
+-----+------+-------+
| 1 | 1001 | 080 |
| 1 | 1002 | 059 |
| 2 | 1002 | 090 |
| 2 | 1003 | 100 |
| 3 | 1001 | 099 |
| 3 | 1003 | 040 |
| 4 | 1001 | 079 |
| 4 | 1002 | 061 |
| 4 | 1003 | 099 |
| 5 | 1003 | 040 |
| 6 | 1001 | 089 |
| 6 | 1003 | 077 |
| 7 | 1001 | 067 |
| 7 | 1003 | 082 |
| 8 | 1001 | 070 |
| 9 | 1003 | 080 |
| 10 | 1003 | 096 |
+-----+------+-------+
17 rows in set (0.00 sec)