大人和孩做爰网站,互联网行业还能支撑多久,网络公司注册条件,网站建设实验分析总结本站以分享各种运维经验和运维所需要的技能为主 《python零基础入门》#xff1a;python零基础入门学习 《python运维脚本》#xff1a; python运维脚本实践 《shell》#xff1a;shell学习 《terraform》持续更新中#xff1a;terraform_Aws学习零基础入门到最佳实战 《k8… 本站以分享各种运维经验和运维所需要的技能为主 《python零基础入门》python零基础入门学习 《python运维脚本》 python运维脚本实践 《shell》shell学习 《terraform》持续更新中terraform_Aws学习零基础入门到最佳实战 《k8》从问题中去学习k8s 《docker学习》暂未更新 《ceph学习》ceph日常问题解决分享 《日志收集》ELK各种中间件 《运维日常》运维日常 《linux》运维面试100问 《DBA》db的介绍使用mysql、redis、mongodb... 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): Cant create database db1; database existsmysql 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_expressionCREATE [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 columnmysql 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_modeNO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
否则数据可能插入完成后并不会显示插入后的内容而是00-00-00.
4数据类型测试
#int类型
mysql create table tb1(id int); # 创建表名为tb1字段名为id类型为intmysql 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)
) ENGINEInnoDB DEFAULT CHARSETutf8 |
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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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: 0mysql 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中
