一、操作表：
增：
语法：
create table 表名(
字段名 列类型 [可选的参数], #记住加逗号
字段名 列类型 [可选的参数], #记住加逗号
字段名 列类型 [可选的参数] #最后一行不加逗号
)
列约束：
auto_increment:自增1
primary key:主键索引，加快查询速度，列的值不能重复
NOT NULL 标识该字段不能为空
DEFAULT 为该字段设置默认值
MySQL中主键和自增的关系：
1、mysql中的主键不一定自增；相反：设置自增属性的列必须是主键，或者加UNIQUE索引
2、主键是有唯一性的，即不可以输入相同的值
整型的每一种都分为：无符号（unsigned）和有符号（signed）两种类型（float和double总是带符号的），在除char以外的数据类型中，默认情况下声明的整型变量都是有符号的类型；char在默认情况下总是无符号的。在除char以外的数据类型中，如果需声明无符号类型的话就需要在类型前加上unsigned。
例子：create database ttt charset utf8;
->use ttt;
->create table t2(
->id int,
->name char(5)
->)charset=utf8;
Query OK, 0 rows affected (0.03 sec)
创建数据表t2成功！
增加数据：
语法：
insert into 表名(列1，列2) values(值1,'值2');
例子：
insert into t2(id,name) values(1,'dudu');
insert into t2(id,name) values(2,'haha');
查询数据：
语法：
select 列1，列2 from 表名;(*代表查询所有列)
例如：select * from t2;
+------+-------+
| id | name |
+------+-------+
| 1 | dudu |
+------+-------+
1 row in set (0.00 sec)
例子：
create table t3(
id int auto_increment primary key,
name char(10)
)charset=utf8;
insert into t3(name) values('ldd');
例子：(推荐)
create table t4(
id int unsigned auto_increment primary key,
name char(10) not null default 'lla',
age int not null default 0
)charset=utf8;
mysql> insert into t4 (age) values (10);
Query OK, 1 row affected (0.05 sec)
mysql> select * from t4;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | lla | 10 |
+----+------+-----+
列类型：
create table 表名(
字段名 列类型 unsigned[可选的参数],
字段名 列类型 [可选的参数],
字段名 列类型 [可选的参数] #最后一行不加逗号
)charset=utf8;
-数字
-整型
tinyint
smallint
int
mediumint
bigint
a.整数类型
b.取值范围
c.unsigned 加上代表不能取负数 只适用于整型
-浮点型
create table t5(
id int auto_increment primary key,
salary decimal(16,10),
num float
)charset=utf8;
float:不一定精确
decimal:非常的精确的数字(5000.23) decimal(6,2) m是数字总个数（负号不算），d是小数点后个数。
正好10位：
insert into t5(salary,num) values (500023.2312345678,5000.23237834567488);
select * from t5;
+----+-------------------+---------+
| id | salary | num |
| 1 | 500023.2312345678 | 5000.23 |
+----+-------------------+---------+
1 row in set (0.00 sec)
少于10位：（会自动补齐10位）
insert into t5(salary,num) values (500023.231234567,5000.23237834567488);
select * from t5;
+----+-------------------+---------+
| id | salary | num |
| 1 | 500023.2312345678 | 5000.23 |
| 2 | 500023.2312345670 | 5000.23 |
+----+-------------------+---------+
1 row in set (0.00 sec)
多于10位：（四舍五入只留10位）
insert into t5(salary,num) values (500023.23123456789,5000.23237834567488);
select * from t5;
+----+-------------------+---------+
| id | salary | num |
| 1 | 500023.2312345678 | 5000.23 |
| 2 | 500023.2312345670 | 5000.23 |
| 3 | 500023.2312345679 | 5000.23 |
+----+-------------------+---------+
1 row in set (0.00 sec)
-字符串
-char（长度）:定长
create table t6(
id int unsigned auto_increment primary key,
name char(10) not null default 'lls'
)charset=utf8;
insert into t6 (name) values ('hello');
insert into t6 (name) values ('hellolkjsadjjj');
select * from t6;
+----+------------+
| id | name |
+----+------------+
| 1 | hello |
+----+------------+
| 2 | hellolkjsa |
+----+------------+
1 row in set (0.00 sec)
-varchar(长度)：变长
create table t7(
id int unsigned auto_increment primary key,
name varchar(10) not null default 'lls'
)charset=utf8;
insert into t7 (name) values ('hello');
insert into t7 (name) values ('hellolkjsadjjj');
select * from t7;
+----+------------+
| id | name |
+----+------------+
| 1 | hello |
+----+------------+
| 2 | hellolkjsa |
+----+------------+
区别：
char：定长，无论插入的字符是多少个，永远固定占规定的长度
场景：
1.身份证
2.手机号 char(11)
3.md5 加密之后的值，比如密码 等 char(32)
varchar:变长，根据插入的字符串的长度来计算所占的字节数，但是有一个字节是用来保存字符大小的
注意：如果不能确定插入的数据的大小，一般建议使用 varchar(255)
- 时间日期类型
YEAR
YYYY（1901/2155）
DATE
YYYY-MM-DD（1000-01-01/9999-12-31）
TIME HH:MM:SS（'-838:59:59'/'838:59:59'）
DATETIME (***************************)
YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
TIMESTAMP
YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）
例子：
create table t8(
d date,
t time,
dt datetime
);
mysql> insert into t8 values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.08 sec)
mysql> select * from t8;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2019-10-29 | 17:06:56 | 2019-10-29 17:06:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
枚举：列出所有的选项
create table t9(
id int auto_increment primary key,
gender enum('male','female')
)charset utf8;
insert into t9 (gender) values ('male');
Query OK, 1 row affected (0.01 sec)
insert into t9 (gender) values ('male');
Query OK, 1 row affected (0.00 sec)
insert into t9 (gender) values ('jasjdhsjncbnv');
warning ...
+----+--------+
| id | name |
+----+--------+
| 1 | male |
+----+--------+
| 2 | female |
+----+--------+
| 3 | |
+----+--------+
改
1. 修改表名
ALTER TABLE 旧表名 RENAME 新表名;
mysql> alter table t8 rename t88;
Query OK, 0 rows affected (0.19 sec)
2. 增加字段
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数],
ADD 字段名 列类型 [可选的参数];
mysql> alter table t5 add gender varchar(32) not null default '';
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+----+-------------------+---------+--------+
| id | salary | num | gender |
+----+-------------------+---------+--------+
| 1 | 500023.2312345678 | 500023 | |
| 2 | 500023.2312345670 | 5000.23 | |
| 3 | 500023.2312345679 | 5000.23 | |
+----+-------------------+---------+--------+
3 rows in set (0.00 sec)
上面添加的列永远是添加在最后一列之后
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] FIRST;
mysql> alter table t5 add name3 varchar(32) not null default '' first;
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+------+----+-------------------+---------+
| name | id | salary | num |
+------+----+-------------------+---------+
| | 1 | 500023.2312345678 | 500023 |
| | 2 | 500023.2312345670 | 5000.23 |
| | 3 | 500023.2312345679 | 5000.23 |
+------+----+-------------------+---------+
3 rows in set (0.00 sec)
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] AFTER 字段名;
mysql> alter table t5 add name4 varchar(32) not null default '' after id;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+------+----+-------+-------------------+---------+
| name | id | name4 | salary | num |
+------+----+-------+-------------------+---------+
| | 1 | | 500023.2312345678 | 500023 |
| | 2 | | 500023.2312345670 | 5000.23 |
| | 3 | | 500023.2312345679 | 5000.23 |
+------+----+-------+-------------------+---------+
3 rows in set (0.00 sec)
3. 删除字段
ALTER TABLE 表名 DROP 字段名;
mysql> alter table t5 drop name4;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+------+----+-------------------+---------+--------+
| name | id | salary | num | gender |
+------+----+-------------------+---------+--------+
| | 1 | 500023.2312345678 | 500023 | |
| | 2 | 500023.2312345670 | 5000.23 | |
| | 3 | 500023.2312345679 | 5000.23 | |
+------+----+-------------------+---------+--------+
3 rows in set (0.00 sec)
4. 修改字段
ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…];
mysql> alter table t5 modify num char(8);
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+------+----+-------------------+---------+--------+
| name | id | salary | num | gender |
+------+----+-------------------+---------+--------+
| | 1 | 500023.2312345678 | 500023 | |
| | 2 | 500023.2312345670 | 5000.23 | |
| | 3 | 500023.2312345679 | 5000.23 | |
+------+----+-------------------+---------+--------+
3 rows in set (0.00 sec)
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
mysql> alter table t6 change name name1 varchar(20) not null default '';
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t6;
+----+------------+
| id | name1 |
+----+------------+
| 1 | hello |
| 2 | hskjdsdcdj |
| 3 | hskjdsdcdj |
+----+------------+
3 rows in set (0.00 sec)
mysql> alter table t6 change name1 name2;
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 '' at line 1
删
drop table 表名; #### 线上禁用
mysql> drop table t7;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+---------------+
| Tables_in_ttt |
+---------------+
| t1 |
| t10 |
| t2 |
| t3 |
| t4 |
| t5 |
| t6 |
| t88 |
| t9 |
+---------------+
9 rows in set (0.01 sec)
复制表结构：
mysql> ## 1. 查看t88表的创建语句
mysql> show create table t88;
+-------+----------------------------------------------------------------------
-------------------------------------------------------------------------------
----------------------------------------+
| Table | Create Table
|
+-------+----------------------------------------------------------------------
-------------------------------------------------------------------------------
----------------------------------------+
| t88 | CREATE TABLE `t88` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL DEFAULT 'lls',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------
-------------------------------------------------------------------------------
----------------------------------------+
1 row in set (0.00 sec)
mysql> ## 2. like
mysql> create table t89 like t88;
Query OK, 0 rows affected (0.02 sec)
mysql> select * from t89;
Empty set (0.00 sec)
mysql> select * from t88;
+----+------------+
| id | name |
+----+------------+
| 1 | hskjdsdcdj |
| 2 | hskjdsdcdj |
+----+------------+
2 rows in set (0.00 sec)
二、操作表数据行：
增：
增加数据：
语法：
insert into 表名(列1，列2) values (值1，'值2');
例子：
insert into t1 (id, name) values (1, 'ldd');
insert into t1 (id, name) values (1, 'whh');
insert into t1 (id, name) values (1, 'ldd2'),(2, 'ldd3'),(3,'ldd4');
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | duodu |
| 1 | duodu |
| 1 | ldd |
| 1 | whh |
| 1 | ldd2 |
| 2 | ldd3 |
| 3 | ldd4 |
+------+-------+
7 rows in set (0.00 sec)
mysql> insert into t5(name) select name1 from t6;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t5;
+------------+----+-------------------+---------+--------+
| name | id | salary | num | gender |
+------------+----+-------------------+---------+--------+
| | 1 | 500023.2312345678 | 500023 | |
| | 2 | 500023.2312345670 | 5000.23 | |
| | 3 | 500023.2312345679 | 5000.23 | |
| hello | 4 | NULL | NULL | |
| hskjdsdcdj | 5 | NULL | NULL | |
| hskjdsdcdj | 6 | NULL | NULL | |
+------------+----+-------------------+---------+--------+
6 rows in set (0.00 sec)
删
delete from 表名 where 条件;
mysql> delete from t5 where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> select * from t5;
+------------+----+-------------------+---------+--------+
| name | id | salary | num | gender |
+------------+----+-------------------+---------+--------+
| | 2 | 500023.2312345670 | 5000.23 | |
| | 3 | 500023.2312345679 | 5000.23 | |
| hello | 4 | NULL | NULL | |
| hskjdsdcdj | 5 | NULL | NULL | |
| hskjdsdcdj | 6 | NULL | NULL | |
+------------+----+-------------------+---------+--------+
5 rows in set (0.00 sec)
mysql> delete from t5 where id>1;
mysql> delete from t5 where id>=1;
mysql> delete from t5 where id<1;
mysql> delete from t5 where id<=1;
mysql> delete from t5 where id>=1 and id<10;
Query OK, 1 row affected (0.06 sec)
delete from 表名; 删除表中所有的数据
mysql> delete from t5;
Query OK, 6 rows affected (0.00 sec)
mysql> select * from t5;
Empty set (0.00 sec)
mysql> insert into t5 (salary, num) values (500023.2312345679, 5000.24);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+----+-------------------+---------+--------+
| name | id | salary | num | gender |
+------+----+-------------------+---------+--------+
| | 8 | 500023.2312345679 | 5000.24 | |
+------+----+-------------------+---------+--------+
1 row in set (0.00 sec)
truncate 表名; #### 没有where条件的
mysql> truncate t5;
Query OK, 0 rows affected (0.25 sec)
mysql> select * from t5;
Empty set (0.00 sec)
mysql> insert into t5 (salary, num) values (500023.2312345679, 5000.24);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t5;
+------+----+-------------------+---------+--------+
| name | id | salary | num | gender |
+------+----+-------------------+---------+--------+
| | 1 | 500023.2312345679 | 5000.24 | |
+------+----+-------------------+---------+--------+
1 row in set (0.00 sec)
区别：
1. delete之后，插入数据从上一次主键自增加1开始， truncate则是从1开始
2. delete删除， 是一行一行的删除， truncate：全选删除 truncate删除的速度是高于delete的
改
update 表名 set 列名1=新值1,列名2=新值2 where 条件;
mysql> update t1 set name='whh' where id=1;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5 Changed: 4 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 2 | ldd3 |
| 3 | ldd4 |
+------+------+
7 rows in set (0.00 sec)
mysql> update t1 set name='ggg' where id>=1;
mysql> update t1 set name='ggg' where id<3;
mysql> update t1 set name='ggg' where id<=3;
查
语法：
select 列1, 列2 from 表名; （*代表查询所有的列）
select * from 表名; （*代表查询所有的列）
mysql> select * from t1 where id>1;
+------+------+
| id | name |
+------+------+
| 2 | ldd3 |
| 3 | ldd4 |
+------+------+
2 rows in set (0.00 sec)
select * from t1 where id=1;
select * from t6 where id<=3;
between..and...: 取值范围是闭区间
mysql> select * from t1 where id between 1 and 3;
+------+------+
| id | name |
+------+------+
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 2 | ldd3 |
| 3 | ldd4 |
+------+------+
7 rows in set (0.00 sec)
避免重复DISTINCT
mysql> select distinct name from t1;
+------+
| name |
+------+
| whh |
| ldd3 |
| ldd4 |
+------+
3 rows in set (0.00 sec)
通过四则运算查询 （不要用）
mysql> select name, age*10 from t3;
+------+--------+
| name | age*10 |
+------+--------+
| ldd | 100 |
+------+--------+
1 row in set (0.01 sec)
mysql> select name, age*10 as age from t3;
+------+-----+
| name | age |
+------+-----+
| ldd | 100 |
+------+-----+
1 row in set (0.02 sec)
in(80,90,100):
mysql> select * from t1 where id in (2,3,4);
+------+------+
| id | name |
+------+------+
| 2 | ldd3 |
| 3 | ldd4 |
+------+------+
2 rows in set (0.00 sec)
like : 模糊查询
以w开头：
mysql> select * from t1 where name like 'w%';
+------+------+
| id | name |
+------+------+
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 1 | whh |
| 1 | whh |
+------+------+
5 rows in set (0.00 sec)
以3结尾：
mysql> select * from t1 where name like '%3';
+------+------+
| id | name |
+------+------+
| 2 | ldd3 |
+------+------+
1 row in set (0.00 sec)
包含d的：
mysql> select * from t1 where name like '%d%';
+------+------+
| id | name |
+------+------+
| 2 | ldd3 |
| 3 | ldd4 |
+------+------+
2 rows in set (0.00 sec)
浙公网安备 33010602011771号