时间：2021-07-01 10:21:17 帮助过：1人阅读
目录
?
? 语法：
? create table 表名（
? 字段名 列类型 [可选的参数]，####记住加逗号
? 字段名 列类型 [可选的参数]，#####记住加逗号
? 字段名 列类型 [可选的参数] #####最后一行不加逗号
? ）charset = utf8; #####后面加分号
? 列约束（*******************************************）
? auto_increment： 自增
? primary key 主键索引，加快查询速度，列的值不能重复
? not null 标识该字段不能为空
? default 为该字段设置默认值
? 例子1：
? create table t1(id int,name char(5))charest = utf8;
? Query OK, 0 rows affected (0.72 sec) #### 如果回显是queryok，代表创建成功
? 增加数据：
? 语法：
? insert into 表名（列1，列2）values（值1，’值2‘）;
? 例子：
? insert into t1(id,name) values(1,‘tank‘);
? insert into t1(id,name) values(1,‘tank2‘);
? 查询数据：
? 语法：
? select 列1，列2 from 表名（*代表查询所有的列）
? 列子：
? mysql>select * from t1;
例子：
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | zekai |
+------+-------+
1 row in set (0.00 sec) ? 例子2：
? create table t2(id int auto_increment primary key,
? name char(10) )charset=utf8;
? 例子3（推荐）
? create table t3(
? id int unsigned auto_increment primary key,
? name char(10) not null default ‘xxx‘,
? age int not null default 0)charset = utf8;
? mysql>insert into t3(age) values(10);
? Query OK, 1 row affected (0.05 sec)
? mysql >select * from t3;
+----+------+-----+
| id | name | age |
+----+------+-----+
| 1 | xxx | 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：非常精确地数字（500.23）decimal（6,2）m是数字总个数（负号不算），d是小数 点后个数。
? 正好10位：
? mysql>:insert into t5(salary,num) values (500023.2312345678, 5000.2374837284783274832);
? Query OK, 1 row affected (0.04 sec)
? mysql>:select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345678 | 5000.24 |
+----+-------------------+---------+
1 row in set (0.00 sec)? 少于10位：
? mysql>: insert into t5(aslary,num) values(500023.231234567, 5000.2374837284783274832);
? Query OK, 1 row affected (0.04 sec)
? mysql>:select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345678 | 5000.24 |
| 2 | 500023.2312345670 | 5000.24 |
+----+-------------------+---------+
多于10位：
mysql> insert into t5 (salary, num) values (500023.23123456789, 5000.2374837284783274832);
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345678 | 5000.24 |
| 2 | 500023.2312345670 | 5000.24 |
| 3 | 500023.2312345679 | 5000.24 |
+----+-------------------+---------+? 字符串
? char(长度)：定长
? create table t6(
? id int unsigned auto_increment primary key,
? name char(10) not null default ‘xxxx‘)charset=utf8;
?
? varchar(长度)：变长
? create table t6 (
? id int auto_increment primary key,
? name varchar(10) not null default ‘xxx‘)charset=utf8;
? mysql>:insert into t6(name) values (‘hello‘);
? Query OK, 1 row affected (0.03 sec)
? mysql>:slect * from t6;
+----+-------+
| id | name |
+----+-------+
| 1 | hello |
+----+-------+? 1 row in set (0.00 sec)
? mysql>insert into t6 (name) values (‘hedsudsjdksdsdsdujudjsjd‘);
? ERROR 1406 (22001): Data too long for column ‘name‘ at row 1
? 区别：
? 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 | 10:49:51 | 2019-10-29 10:49:51 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
insert into t8 values(now(),now(),now());? 枚举
? 列出所有的选项
? create table t9(
? id int auto_increment primary key,
? gender enum(‘male‘,‘female‘)
? )charset = utf8;
? mysql>:insert into t9 (gender) values(‘male‘);
? Query OK, 1 row affected (0.04 sec)
? mysql>:insert into t9(gender) values(‘female‘);
? Query OK, 1 row affected (0.03 sec)
? mysql>:insert into t9(gender) values(‘sgcdtcdsgdcsd‘)
? 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 t88 add nmae varchar(32) not null default ‘ ‘;
Query OK, 0 rows affected (0.82 sec)
Records: 0 Duplicates: 0 Warnings: 0? 上面添加的列永远是添加在最后一列之后
?
ALTER TABLE 表名
ADD 字段名 列类型 [可选的参数] FIRST;
mysql> alter table t88 add name3 varchar(32) not null default '' first;
Query OK, 0 rows affected (0.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
alter table 表名
add 字段名 列类型 [可选的参数] after 字段名;
mysql>:alter table t88 add name4 varchar(32) not null default '' after id;
Query OK, 0 rows affected (0.68 sec)
Records: 0 Duplicates: 0 Warnings: 0
3 删除字段
alter table 表名 modify 字段名 数据类型 [完整性约束条件];
mysql>alter table t88 modify name2 char(20);
Query OK, 1 row affected (0.88 sec)
Records: 1 Duplicates: 0 Warnings: 0
4修改字段
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件];
mysql>alter table t88 change name2 name22 varchar(32) not null default'';
Query OK, 1 row affected (0.82 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql>alter table t88 change name22 name23;
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 t9;
Query OK, 0 rows affected (0.18 sec)?
?
mysql>show table;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
复制表结构：
mysql> ##1 查看t88表的创建语句
mysql>show create table t88;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t88 | CREATE TABLE `t88` (
`name3` varchar(32) NOT NULL DEFAULT '',
`d` date DEFAULT NULL,
`t` time DEFAULT NULL,
`dt` datetime DEFAULT NULL,
`name` varchar(32) NOT NULL DEFAULT '',
`name22` varchar(32) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> ## 2. like
mysql> create table t89 like t88;
Query OK, 0 rows affected (0.33 sec)
增加数据
语法：
insert into 表名（列1，列2）values（值1，‘值2’）;
例子：
insert into t1(id,name) values(1,'tank');
insert into t1(id,name) values(1,'tank2');
insert into t1(id,name) values(1,'tank3');? mysql>insert into t66(name) select name from t6;
? Query OK, 4 rows affected (0.09 sec)
? Records: 4 Duplicates: 0 Warnings: 0
delete from 表名 where 条件：
mysq>delect from t5 where id=1;
mysql>delect from t5 where id>1;
mysql>delect from t5 where id>=1;
mysql>delect from t5 where id<=1;
mysql>delect from t5 wher id<1;
mysql>delect from t5 wher id>=1 and id<10;
Query OK, 1 row affected (0.06 sec)
delect from 表名;删除表中所有的数据
mysql>insert into t5(salary,num) values(500023.2312345679, 5000.24);
Query OK, 1 row affected (0.08 sec
mysql>select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 4 | 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.06 sec)
mysql>select * from t5;
+----+-------------------+---------+
| id | salary | num |
+----+-------------------+---------+
| 1 | 500023.2312345679 | 5000.24 |
+----+-------------------+---------+
1 row in set (0.00 sec)
区别：
1 delect之后，插入数据从上一次主键自增加1开始，truncate则是从1开始
2delect删除，是一行一行的删除，truncate：全选删除，truancy删除的速度是高于delect的
?
?
? undate 表名 set 列名1=新值1，列名2=新值2，where 条件;
? mysql>update t66 set name=‘xxx‘ where id<30;
? Query OK, 1 row affected (0.04 sec)
? Rows matched: 1 Changed: 1 Warnings: 0
?
? mysql>update t66 set name=‘xxx‘ where id<30;
? mysql>update t66 set name=‘xxx‘ where id<=30;
? mysql>update t66 set name=‘xxx‘ where id>=30;
? mysql>update t66 set name=‘xxx‘ where id>30;
? mysql>update t66 set name=‘xxx‘ where id>20 and id<32;
? mysql>update t66 set name=‘xxx‘where id>20 or name=‘tank‘;
? Query OK, 1 row affected (0.04 sec)
? Rows matched: 1 Changed: 1 Warnings: 0
?
?
? 语法：
? select 列1，列2 from 表名;(代表查询所有列)
? select from t66 where id>30 and id<40;
? select * from t66 where id>30;
? select * from t66 where id<30;
? select * from t66 where id<=30;
? select * from t66 where id>=30;
? select * from t66 where id!=30;
? select * from t66 where id<>30;
? mysql>select * from from t1;
? +------+-------+
? | id | name |
? +------+-------+
? | 1 | zekai |
? +------+-------+
? 1 row in set (0.00 sec)
?
between...and....取值范围是闭区间?
? select * from where id between 30 and 40；
? mysql>select * from t66 where id between 31 and 33;
? +----+--------+
? | id | name |
? +----+--------+
? | 31 | dsadsa |
? | 32 | dsadsa |
? | 33 | dsadsa |
? +----+--------+
? 避免重复distinct
? mysql>select distinct name from t66;
+--------+
| name |
+--------+
| xxxx |
| hds |
| dsadsa |
+--------+
3 rows in set (0.00 sec)
通过四则运算查询 （不要用）
mysql> select name, age*10 from t3;
+------+--------+
| name | age*10 |
+------+--------+
| xxx | 100 |
+------+--------+
1 row in set (0.01 sec)
mysql> select name, age*10 as age from t3;
+------+-----+
| name | age |
+------+-----+
| xxx | 100 |
+------+-----+
1 row in set (0.02 sec)
in(80,90,100):
mysql> select * from t66 where id in (23,34,11);
+----+------+
| id | name |
+----+------+
| 11 | xxxx |
| 23 | hds |
+----+------+
2 rows in set (0.04 sec)
like:模糊查询
以x开头：
mysql>select * from t66 where name like 'x%';
+----+------+
| id | name |
+----+------+
| 1 | xxxx |
| 2 | xxxx |
| 3 | xxxx |
| 4 | xxxx |
| 8 | xxxx |
| 9 | xxxx |
| 10 | xxxx |
| 11 | xxxx |
| 15 | xxxx |
| 16 | xxxx |
| 17 | xxxx |
| 18 | xxxx |
| 30 | xxxx |
+----+------+
13 rows in set (0.05 sec)?
? 以x结尾：
? mysql>select * from t66 where name like ‘%x‘;
?
+----+------+
| id | name |
+----+------+
| 1 | xxxx |
| 2 | xxxx |
| 3 | xxxx |
| 4 | xxxx |
| 8 | xxxx |
| 9 | xxxx |
| 10 | xxxx |
| 11 | xxxx |
| 15 | xxxx |
| 16 | xxxx |
| 17 | xxxx |
| 18 | xxxx |
| 30 | xxxx |
+----+------+
13 rows in set (0.00 sec)?
? 包含x的：
? mysql>select * from t66 where name like ‘%x%;
?
不让用 1. 查看岗位是teacher的员工姓名、年龄
select name,age from staff_info where jobs = 'teacher';
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from staff_info where jobs = 'teacher' and age > 30;
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
select name,age,salary from staff_info where jobs = 'teacher' and salary between 9000 and 10000;
4. 查看岗位描述不为NULL的员工信息
select * from staff_info where jobs_info is not null;
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from staff_info where jobs = 'teacher' and salary in(10000,9000,30000);
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from staff_info where jobs = 'teacher' and salary not in(10000,9000,3000);
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary from staff_info where jobs = 'teacher' and name like jin%;数据库表操作与列操作
标签：操作 lte pre 整数 syn war char warning 完整性