day38
mysql表的创建
案例
学号 姓名 性别 电话 成绩 9527 zhouxingxing male 119 99 9528 qiuxiang female 110 80 9529 shiliu female 114 59 语法
create table 表名( 列明 数据类型 [约束], 列名 数据类型 [约束], ... 列名 数据类型 [约束] )[charset = utf8];create table student( sid int, name varchar(20), gender char(6), tel char(11), score double );
数据表的修改
语法
alter table 表名 操作;向现有表中添加列
向student表中添加一个age字段，age的类型是整数
alter table student add age int;修改表中的列
alter table student modify gender char(4);删除表中的咧
alter table student drop abc;修改列名
alter table student change gender sex char(4);注意：修改列名时，在给定的新列名的同时要指定数据类型和约束 修改表名
alter table student rename tb_stu;
DML
DML data manipulate language数据操作语言
cud都被称之为数据操作语言
新增，insert
语法 insert into 表名(列1,列2,列3,...) values(值1,值2,值3,...);
学号 姓名 性别 电话 成绩 年龄 9527 zhouxingxing male 119 99 20 9528 qiuxiang female 110 80 18 9529 shiliu female 114 59 30 insert into tb_stu(sid, name, sex, tel, score, age) values('9527', 'zhouxingxing', 'boy', '119', 99, 20); insert into tb_stu(sid, name, sex, tel, score, age) values(9529, 'shiliu', 'girl', 114, 59, 30); insert into tb_stu values(9530, 'chunxiang', 'girl', '116', 77, 19); // 如果插入时不加字段，则对于表里的所有字段添加值 insert into tb_stu(sid, name, sex, tel) values(9528, 'qiuxiang', 'female', 110); // 值的长度不能超过限制 insert into tb_stu(sid, name, sex, tel) values(9528, 'qiuxiang', 'girl', 110); // 列的数量和类型与值的数量和类型要一致 insert into tb_stu(sid, name, sex, tel) values(9528, 'qiuxiang', 'girl', 110, 99.99); // 前面是四个字段，后面必须是四个值 insert into tb_stu(sid, name, sex, tel) values('abc', 'qiuxiang', 'girl', 110); // abc作为字符串无法转换成int类型的数值 insert into tb_stu(sid, name, sex, tel, score, age) values(9529, shiliu, girl, 114, 59, 30); // 字符串类型的值必须用引号引起来注意：表名后面的列名与values中的值要一一对应（个数，顺序和类型）
修改， update
语法： update 表名 set 列1 = 值1，列2 = 值2, ... where 条件update tb_stu set score = 88 where sid = 9528; // 将sid为9528的score设置为88 update tb_stu set score = 90, age = 19 where sid = 9528; // 将sid为9528的score设置为88,age设置为19 update tb_stu set score = 88 ; // 没有给where条件，则整张表的所有的score值都设置88注意：set后多个列名=值，绝大多数情况下都要添加where条件，指定修改，否则为整表更新
删除，delete
语法： delete from 表名 where 条件; delete from tb_stu where sid = 9530;注意：删除时，如果不加where条件，删除的就是整张表的数据
清空数据库: truncate
语法： truncate table 表名;truncate与delete的区别
两个命令都是删除了所有的数据：
truncate先销毁表，然后再创建一个相同结构的表
delete没有条件也是删除了所有数据，但是它时逐条删除表中的数据
mysql> select database(); +------------+ | database() | +------------+ | NULL | +------------+ 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | db_test01 | | db_test02 | | db_test03 | | library_db | | moneydb | | mysql | | performance_schema | | saas | | saas01 | | saas02 | | schooldb | | studb | | sys | | test1 | +--------------------+ 15 rows in set (0.00 sec) mysql> use saas; Database changed mysql> select database(); +------------+ | database() | +------------+ | saas | +------------+ 1 row in set (0.00 sec) mysql> show tables; +----------------+ | Tables_in_saas | +----------------+ | tb_stu | +----------------+ 1 row in set (0.00 sec) mysql> desc tb_stu; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(4) | YES | | NULL | | | tel | char(11) | YES | | NULL | | | score | double | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> insert into tb_stu(sid, name, sex, tel, score, age) values('9527', 'zhouxingxing', 'boy', '119', 99, 20); Query OK, 1 row affected (0.01 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | +------+--------------+------+------+-------+------+ 1 row in set (0.00 sec) mysql> desc tb_stu; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(4) | YES | | NULL | | | tel | char(11) | YES | | NULL | | | score | double | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> insert into tb_stu(sid, name, sex, tel) values(9528, 'qiuxiang', 'female', 110); ERROR 1406 (22001): Data too long for column 'sex' at row 1 mysql> insert into tb_stu(sid, name, sex, tel) values(9528, 'qiuxiang', 'girl', 110); Query OK, 1 row affected (0.02 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | NULL | NULL | +------+--------------+------+------+-------+------+ 2 rows in set (0.00 sec) mysql> insert into tb_stu(sid, name, sex, tel) values(9528, 'qiuxiang', 'girl', 110, 99.99); ERROR 1136 (21S01): Column count doesn't match value count at row 1 mysql> desc tb_stu; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | sid | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | char(4) | YES | | NULL | | | tel | char(11) | YES | | NULL | | | score | double | YES | | NULL | | | age | int(11) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) mysql> insert into tb_stu(sid, name, sex, tel) values('abc', 'qiuxiang', 'girl', 110); ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'sid' at row 1 mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | NULL | NULL | +------+--------------+------+------+-------+------+ 2 rows in set (0.00 sec) mysql> insert into tb_stu(sid, name, sex, tel, score, age) values(9529, shiliu, girl, 114, 59, 30); ERROR 1054 (42S22): Unknown column 'shiliu' in 'field list' mysql> insert into tb_stu(sid, name, sex, tel, score, age) values(9529, 'shiliu', 'girl', 114, 59, 30); Query OK, 1 row affected (0.01 sec) mysql> insert into tb_stu values(9530, 'chunxiang', 'girl', '116', 77, 19); Query OK, 1 row affected (0.01 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | NULL | NULL | | 9529 | shiliu | girl | 114 | 59 | 30 | | 9530 | chunxiang | girl | 116 | 77 | 19 | +------+--------------+------+------+-------+------+ 4 rows in set (0.00 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | NULL | NULL | | 9529 | shiliu | girl | 114 | 59 | 30 | | 9530 | chunxiang | girl | 116 | 77 | 19 | +------+--------------+------+------+-------+------+ 4 rows in set (0.00 sec) mysql> update tb_stu set score = 88 where sid = 9528; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 88 | NULL | | 9529 | shiliu | girl | 114 | 59 | 30 | | 9530 | chunxiang | girl | 116 | 77 | 19 | +------+--------------+------+------+-------+------+ 4 rows in set (0.00 sec) mysql> update tb_stu set score = 88, age = 19 where sid = 9528; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 88 | 19 | | 9529 | shiliu | girl | 114 | 59 | 30 | | 9530 | chunxiang | girl | 116 | 77 | 19 | +------+--------------+------+------+-------+------+ 4 rows in set (0.00 sec) mysql> update tb_stu set score = 90, age = 19 where sid = 9528; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 90 | 19 | | 9529 | shiliu | girl | 114 | 59 | 30 | | 9530 | chunxiang | girl | 116 | 77 | 19 | +------+--------------+------+------+-------+------+ 4 rows in set (0.00 sec) mysql> update tb_stu set score = 88 ; Query OK, 4 rows affected (0.01 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 88 | 20 | | 9528 | qiuxiang | girl | 110 | 88 | 19 | | 9529 | shiliu | girl | 114 | 88 | 30 | | 9530 | chunxiang | girl | 116 | 88 | 19 | +------+--------------+------+------+-------+------+ 4 rows in set (0.00 sec) mysql> delete from tb_stu where sid = 9530; Query OK, 1 row affected (0.01 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 88 | 20 | | 9528 | qiuxiang | girl | 110 | 88 | 19 | | 9529 | shiliu | girl | 114 | 88 | 30 | +------+--------------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> truncate table tb_stu; Query OK, 0 rows affected (0.01 sec) mysql> select * from tb_stu; Empty set (0.00 sec)
数据查询
DQL： data query language: 数据查询语言，不会修改表中的数据，可以将表中的数据展示出来
数据库表的基本结构
关系型数据库以表格（table）形式进行数据存储，表格由“行”和“列”组成
执行查询语句返回的结果集是一张虚拟表
基本查询
语法： select 列名 from 表名;select * from tb_stu; // *代表查询所有字段，真实项目中，数据量很大的情况下，不建议使用*，即使查询所有，也建议将所有的列排列出来可以对于指定列进行运算：
select name, score + 1 from tb_stu; // 将每一行score加一后显示起别名：
语法： 列 as '列名'select sid as '学号', name as '姓名', sex as '性别', tel as '电话', score as '成绩', age as '年龄' from tb_stu; select sid '学号', name '姓名', sex '性别', tel '电话', score '成绩', age '年龄' from tb_stu; // as也可以省略mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25 Server version: 5.7.43-log MySQL Community Server (GPL) Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use saas ; Database changed mysql> select * from tb_stu; Empty set (0.00 sec) mysql> insert into tb_stu values(9527, 'zhouxingxing', 'boy', 119, 99, 20); Query OK, 1 row affected (0.01 sec) mysql> insert into tb_stu values(9528, 'qiuxiang', 'girl', 110, 80, 18); Query OK, 1 row affected (0.00 sec) mysql> insert into tb_stu values(9527, 'shiliu', 'girl', 114, 59, 30); Query OK, 1 row affected (0.01 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9527 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> update tb_stu sid = 9529 where name = 'shiliu'; ERROR 1064 (42000): 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 '= 9529 where name = 'shiliu'' at line 1 mysql> update tb_stu set sid = 9529 where name = 'shiliu'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9529 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> select sid from tb_stu; +------+ | sid | +------+ | 9527 | | 9528 | | 9529 | +------+ 3 rows in set (0.00 sec) mysql> select name from tb_stu; +--------------+ | name | +--------------+ | zhouxingxing | | qiuxiang | | shiliu | +--------------+ 3 rows in set (0.00 sec) mysql> select sid, name, tel from tb_stu; +------+--------------+------+ | sid | name | tel | +------+--------------+------+ | 9527 | zhouxingxing | 119 | | 9528 | qiuxiang | 110 | | 9529 | shiliu | 114 | +------+--------------+------+ 3 rows in set (0.00 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9529 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> select score from tb_stu; +-------+ | score | +-------+ | 99 | | 80 | | 59 | +-------+ 3 rows in set (0.00 sec) mysql> select name, score from tb_stu; +--------------+-------+ | name | score | +--------------+-------+ | zhouxingxing | 99 | | qiuxiang | 80 | | shiliu | 59 | +--------------+-------+ 3 rows in set (0.00 sec) mysql> select name, score + 1 from tb_stu; +--------------+-----------+ | name | score + 1 | +--------------+-----------+ | zhouxingxing | 100 | | qiuxiang | 81 | | shiliu | 60 | +--------------+-----------+ 3 rows in set (0.01 sec) mysql> select 'saas_' + name, score + 1 from tb_stu; +----------------+-----------+ | 'saas_' + name | score + 1 | +----------------+-----------+ | 0 | 100 | | 0 | 81 | | 0 | 60 | +----------------+-----------+ 3 rows in set, 6 warnings (0.00 sec) mysql> select concat('saas_', name), score + 1 from tb_stu; +-----------------------+-----------+ | concat('saas_', name) | score + 1 | +-----------------------+-----------+ | saas_zhouxingxing | 100 | | saas_qiuxiang | 81 | | saas_shiliu | 60 | +-----------------------+-----------+ 3 rows in set (0.00 sec) mysql> select * from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9529 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> select sid, name, sex, tel, score, age from tb_stu; +------+--------------+------+------+-------+------+ | sid | name | sex | tel | score | age | +------+--------------+------+------+-------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9529 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+-------+------+ 3 rows in set (0.00 sec) mysql> select sid as '学号', name as '姓名', sex as '性别', tel as '电话', score as '成绩', age as '年龄' from tb_stu; +------+--------------+------+------+------+------+ | 学号 | 姓名 | 性别 | 电话 | 成绩 | 年龄 | +------+--------------+------+------+------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9529 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+------+------+ 3 rows in set (0.00 sec) mysql> select sid '学号', name '姓名', sex '性别', tel '电话', score '成绩', age '年龄' from tb_stu; +------+--------------+------+------+------+------+ | 学号 | 姓名 | 性别 | 电话 | 成绩 | 年龄 | +------+--------------+------+------+------+------+ | 9527 | zhouxingxing | boy | 119 | 99 | 20 | | 9528 | qiuxiang | girl | 110 | 80 | 18 | | 9529 | shiliu | girl | 114 | 59 | 30 | +------+--------------+------+------+------+------+ 3 rows in set (0.00 sec)