文章目录
- 1.建表(DDL语言)：create table
- 2.显示表结构：describe table;
- 3.修改表结构：alter table
- 4.表的crud操作(DML语言)：update ，delete
1.建表(DDL语言)：create table
- 建表，建表要制定数据库是哪一个数据库的
create table study.t_emp(empno int, ename varchar(20), esex char(2));
study.t_emp，数据库study，表名t_emp
表的字段，字段的类型
empno int,
ename varchar<20>,
esex char<2>
varchar<20>表示可变字符串，表示你插入的字符是多少个，那么你占用的空间就是多少个字符
char<2>表示定长的2个字符，定长表示在数据库中存储的空间是2个字符，但是你插入的字符可以是1个，
只是他占用的空间是2个而已
- 创建表，但是不需要指定数据库名称的方法
use study;
create table t_emp(empno int, ename varchar(20), esex char(2));
指定数据库查表：
show tables from study;
不指定数据库查表：
use study;
show tables;
2.显示表结构：describe table;
use study;
describe t_emp;
desc t_emp;
3.修改表结构：alter table
use study;
将varchar<20>修改成varchar<30>
alter table t_emp modify ename varchar(30);
删除esex字段
alter table t_emp drop esex;
添加esex字段
alter table t_emp add esex char(2);
4.表的crud操作(DML语言)：update ，delete
- 指增加(Create)、查询(Retrieve)（重新得到数据）、更新(Update)和删除(Delete)
插入一条记录
mysql> desc t_emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| empno | int | YES | | NULL | |
| ename | varchar(30) | YES | | NULL | |
| esex | char(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
insert into t_emp(empno,ename,esex) values(1000,'Tom','m');
insert into t_emp values(1000,'Maggle','f');
insert into t_emp(empno,ename) values(1000,'Tom');
insert into t_emp(empno,ename,esex) values(1000,null,'m');
mysql> select * from t_emp;
+-------+--------+------+
| empno | ename | esex |
+-------+--------+------+
| 1000 | Tom | m |
| 1000 | Maggle | f |
| 1000 | Tom | NULL |
| 1000 | NULL | m |
+-------+--------+------+
4 rows in set (0.00 sec)
插入中文字段失败的原因是：编码方式不是gbk
查看所有的字符集
mysql> show variables like 'character_set%';
+--------------------------+--------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb3 |
| character_set_filesystem | binary |-----这里的二进制指的是/var/lib/mysql/study/t_emp.ibd数据是二进制的
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
为了能够插入中文，可以将编码方式改为gbk;
mysql> set names gbk;
mysql> insert into t_emp values(1,'王继','f');
mysql> select * from t_emp;
+-------+--------+------+
| empno | ename | esex |
+-------+--------+------+
| 1000 | Tom | m |
| 1000 | Maggle | f |
| 1000 | Tom | NULL |
| 1000 | NULL | m |
| 1 | 王继 | f |
+-------+--------+------+
5 rows in set (0.00 sec)
- update命令
将Tom记录的empno字段改为1001
mysql> update t_emp set empno=1001 where ename='Tom';
mysql> select * from t_emp;
+-------+-----------+------+
| empno | ename | esex |
+-------+-----------+------+
| 1001 | Tom | m |
将esex为m的改为f
mysql> update t_emp set esex='f' where esex='m';
mysql> select * from t_emp;
+-------+--------+------+
| empno | ename | esex |
+-------+--------+------+
| 1001 | Tom | f |
| 1000 | Maggle | f |
| 1001 | Tom | NULL |
| 1000 | NULL | f |
| 1 | 王继 | f |
+-------+--------+------+
5 rows in set (0.00 sec)
- delete
删除表中esex为null记录删除掉
mysql> delete from t_emp where esex is null;
Query OK, 1 row affected (0.06 sec)
mysql> select * from t_emp;
+-------+--------+------+
| empno | ename | esex |
+-------+--------+------+
| 1001 | Tom | f |
| 1000 | Maggle | f |
| 1000 | NULL | f |
| 1 | 王继 | f |
+-------+--------+------+
4 rows in set (0.00 sec)
没有加条件的话，将整张表的记录全部删除
mysql> delete from t_emp;
Query OK, 4 rows affected (0.05 sec)
mysql> select * from t_emp;
Empty set (0.00 sec)
- sql语句如下：test.sql
DDL语言
create table t_emp(empno int, ename varchar(20), esex char(2));
alter table t_emp modify ename varchar(30);
alter table t_emp drop esex;
alter table t_emp add esex char(2);
DML语言:给表增加一些记录
insert into t_emp(empno, ename, esex) values(1000,'tom','m');
insert into t_emp(empno, ename, esex) values(1000,'maggie','f');
insert into t_emp(empno, ename) values(1000,'tom');
insert into t_emp(empno, ename, esex) values(1000,null,'m');
insert into t_emp(empno, ename, esex) values(1000,'张三','男');
show variables like 'character_set%';
查看所有的字符集
set names gbk;
update t_emp set empno=1001 where ename='maggie';
delete from t_emp where esex is null;
删除表的所有记录
delete from t_emp;
删除t_emp整张表
注意：整数不用引号，字符串需要
drop table t_emp;
