本站以分享各种运维经验和运维所需要的技能为主
《python零基础入门》:python零基础入门学习
《python运维脚本》: python运维脚本实践
《shell》:shell学习
《terraform》持续更新中:terraform_Aws学习零基础入门到最佳实战
《k8》从问题中去学习k8s
《docker学习》暂未更新
《ceph学习》ceph日常问题解决分享
《日志收集》ELK+各种中间件
《运维日常》运维日常
《linux》运维面试100问
SQL语句
一、sql语句的语义种类
DDL: 数据定义语言 Data Definition Language DCL: 数据控制语言 Data Control Language DML: 数据操作语言 Data Manipulate Language DQL: 数据查询语言 Data Query Language
二、DDL: 数据定义语言 (create、drop)
1.create针对库的操作
1)语法
mysql> help create database Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
2)创建库
mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> create SCHEMA db2; Query OK, 1 row affected (0.00 sec)
3)建库时提示已存在
mysql> create database db1; ERROR 1007 (HY000): Can't create database 'db1'; database exists mysql> create database IF NOT EXISTS db1; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> create database IF NOT EXISTS db1; Query OK, 1 row affected, 1 warning (0.00 sec)
4)查看建库语句
mysql> show create database db1; +----------+----------------------------------------------------------------+ | Database | Create Database | +----------+----------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+----------------------------------------------------------------+ 1 row in set (0.00 sec)
5)创建数据库并指定字符集
mysql> create database db3 charset utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> show create database db3; +----------+--------------------------------------------------------------+ | Database | Create Database | +----------+--------------------------------------------------------------+ | db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+--------------------------------------------------------------+ 1 row in set (0.00 sec) #不指定校验规则默认就是 utf8_general_ci mysql> create database db4 charset utf8; Query OK, 1 row affected (0.00 sec)
6)删库
mysql> drop database db1; Query OK, 0 rows affected (0.00 sec)
7)修改库
mysql> show create database db2; # 查看数据库使用字符集 mysql> alter database db2 charset utf8 collate utf8_general_ci; # 修改字符集
2.create针对表的操作
1)语法
mysql> help create table; Name: 'CREATE TABLE' Description: Syntax: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [partition_options] [IGNORE | REPLACE] [AS] query_expression CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
2)建表
#1.进入一个库 mysql> use db2 #2.查看当前所在库 mysql> select database(); #3.建表,建表最少有一列 mysql> create table tb1; ERROR 1113 (42000): A table must have at least 1 column mysql> create table tb1(id int); Query OK, 0 rows affected (0.04 sec) #4.查看表 mysql> show tables; # 查看库中有多少表 mysql> desc tb1; # 查看表中字段
3)数据类型
int 整数 -2^31 - 2^31-1 (-2147483648 - 2147483647) 不能超出此范围 bigint 较大整数数据类型 (-2^63 - 2^63-1) 比int整数类型可用范围广 tinyint 最小整数 -128 - 127 #年龄 0 - 255 varchar 字符类型(变长) #身份证 char 字符类型(定长) enum 枚举类型 #给它固定选项,只能选则选择项中的值 性别 datetime 时间类型 年月日时分秒 注意: 如果使用datetime不是为now()当前时间,而是直接书写时间,如2020-03-01,则需要在配置文件中添加此行信息sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 否则数据可能插入完成后,并不会显示插入后的内容,而是00-00-00.
4)数据类型测试
#int类型 mysql> create table tb1(id int); # 创建表名为tb1,字段名为id,类型为int mysql> insert tb1 values(1); # 在tb1表中插入对应id字段的值为1 #enum类型 mysql> create table qiudao(id int,sex enum('nan','nv')); Query OK, 0 rows affected (0.02 sec) mysql> insert into qiudao values(1,'nan'); Query OK, 1 row affected (0.00 sec) mysql> insert into qiudao values(1,'qiudao'); ERROR 1265 (01000): Data truncated for column 'sex' at row 1
5)建表测试
表名:student id name age gender cometime #1.建表 mysql> create table student( -> id int, -> name varchar(12), -> age tinyint, -> gender enum('M','F'), -> cometime datetime); Query OK, 0 rows affected (0.01 sec) #2.插入数据 mysql> insert into student values(1,'邱导',-18,'M',now()); Query OK, 1 row affected (0.00 sec)
6)建表数据属性
not null: #非空 primary key: #主键(唯一且非空的) auto_increment: #自增(此列必须是:primary key或者unique key) unique key: #唯一键,单独的唯一的 default: #默认值 unsigned: #非负数 comment: #注释
7)加上属性建表
#1.建表语句 create table students( id int primary key auto_increment comment "学生id", name varchar(12) not null comment "学生姓名", age tinyint unsigned not null comment "学生年龄", gender enum('M','F') default 'M' comment "学生性别", cometime datetime default now() comment "入学时间"); #2.查看建表语句 mysql> show create table students; | students | CREATE TABLE `students` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id', `name` varchar(12) NOT NULL COMMENT '学生姓名', `age` tinyint(3) unsigned NOT NULL COMMENT '学生年龄', `gender` enum('M','F') DEFAULT 'M' COMMENT '学生性别', `cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 1 row in set (0.00 sec) #3.插入数据 mysql> insert into students values(1,'qiudao',18,'M',now()); Query OK, 1 row affected (0.00 sec) #因为主键相同无法插入 mysql> insert into students values(1,'qiudao',18,'M',now()); ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' #应该 mysql> insert into students values('2','qiudao',18,'M',now()); Query OK, 1 row affected (0.00 sec) #主键已经设置自增没必要自己插入 #正规插入数据的写法 mysql> insert students(name,age) values('lhd',18); Query OK, 1 row affected (0.00 sec) mysql> insert students(name,age) values('lhd',12); Query OK, 1 row affected (0.01 sec) mysql> select * from students; +----+--------+-----+--------+---------------------+ | id | name | age | gender | cometime | +----+--------+-----+--------+---------------------+ | 1 | qiudao | 18 | M | 2020-07-14 19:51:44 | | 2 | qiudao | 18 | M | 2020-07-14 19:52:19 | | 3 | lhd | 18 | M | 2020-07-14 19:53:50 | | 4 | lhd | 12 | M | 2020-07-14 19:53:58 | +----+--------+-----+--------+---------------------+ 4 rows in set (0.00 sec)
3.删除表
drop table student;
4.修改表
#1.新建表 mysql> create table linux(daijiadong tinyint); Query OK, 0 rows affected (0.04 sec #2.修改表名 mysql> alter table linux rename linux9; Query OK, 0 rows affected (0.01 sec) #3.插入新字段 mysql> alter table linux9 add rengyufeng int; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------+------+-----+---------+-------+ | daijiadong | tinyint(4) | YES | | NULL | | | rengyufeng | int(11) | YES | | NULL | | +------------+------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) #4.插入多个新字段 mysql> alter table linux9 add liukong int,add wangzhangxing int; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +---------------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------+------+-----+---------+-------+ | daijiadong | tinyint(4) | YES | | NULL | | | rengyufeng | int(11) | YES | | NULL | | | liukong | int(11) | YES | | NULL | | | wangzhangxing | int(11) | YES | | NULL | | +---------------+------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) #5.插入字段到最前面 mysql> alter table linux9 add kangpeiwen varchar(100) first; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | kangpeiwen | varchar(100) | YES | | NULL | | | daijiadong | tinyint(4) | YES | | NULL | | | rengyufeng | int(11) | YES | | NULL | | | liukong | int(11) | YES | | NULL | | | wangzhangxing | int(11) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 5 rows in set (0.01 sec) #6.插入字段到指定字段后面 mysql> alter table linux9 add chenjianqing varchar(100) after daijiadong; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | kangpeiwen | varchar(100) | YES | | NULL | | | daijiadong | tinyint(4) | YES | | NULL | | | chenjianqing | varchar(100) | YES | | NULL | | | rengyufeng | int(11) | YES | | NULL | | | liukong | int(11) | YES | | NULL | | | wangzhangxing | int(11) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 6 rows in set (0.00 sec) #7.删除指定列 mysql> alter table linux9 drop daijiadong; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | kangpeiwen | varchar(100) | YES | | NULL | | | chenjianqing | varchar(100) | YES | | NULL | | | rengyufeng | int(11) | YES | | NULL | | | liukong | int(11) | YES | | NULL | | | wangzhangxing | int(11) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) #8.修改字段 mysql> alter table linux9 change rengyufeng congtianqi int; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | kangpeiwen | varchar(100) | YES | | NULL | | | chenjianqing | varchar(100) | YES | | NULL | | | congtianqi | int(11) | YES | | NULL | | | liukong | int(11) | YES | | NULL | | | wangzhangxing | int(11) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 5 rows in set (0.00 sec) #9.修改字段属性 mysql> alter table linux9 modify congtianqi tinyint; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc linux9; +---------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+--------------+------+-----+---------+-------+ | kangpeiwen | varchar(100) | YES | | NULL | | | chenjianqing | varchar(100) | YES | | NULL | | | congtianqi | tinyint(4) | YES | | NULL | | | liukong | int(11) | YES | | NULL | | | wangzhangxing | int(11) | YES | | NULL | | +---------------+--------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)
三、复制表结构
# 对数据库的表结构拷贝 mysql> create table ttt like student; # 创建一个新的表叫ttt,和student的表结构一致,但不会将student的数据拷贝过来 Query OK, 0 rows affected (0.10 sec) # 对数据库数据拷贝 mysql> insert into ttt select * from student; # 将查询出来的结果,全部导入到ttt表中 # 对导入数据筛选 mysql> insert into ttt select * from student where age > 22; # 对筛选结果导入到表ttt中