網(wǎng)站 建設(shè) 成品小程序開發(fā)平臺官網(wǎng)
?本站以分享各種運(yùn)維經(jīng)驗(yàn)和運(yùn)維所需要的技能為主
《python零基礎(chǔ)入門》：python零基礎(chǔ)入門學(xué)習(xí)
《python運(yùn)維腳本》：?python運(yùn)維腳本實(shí)踐
《shell》：shell學(xué)習(xí)
《terraform》持續(xù)更新中：terraform_Aws學(xué)習(xí)零基礎(chǔ)入門到最佳實(shí)戰(zhàn)
《k8》從問題中去學(xué)習(xí)k8s
《docker學(xué)習(xí)》暫未更新
《ceph學(xué)習(xí)》ceph日常問題解決分享
《日志收集》ELK+各種中間件
《運(yùn)維日?！愤\(yùn)維日常
《linux》運(yùn)維面試100問
《DBA》db的介紹使用（mysql、redis、mongodb...）
SQL語句
一、sql語句的語義種類
DDL: 數(shù)據(jù)定義語言 Data Definition Language DCL: 數(shù)據(jù)控制語言 Data Control Language DML: 數(shù)據(jù)操作語言 Data Manipulate Language DQL: 數(shù)據(jù)查詢語言 Data Query Language
二、DDL: 數(shù)據(jù)定義語言 （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）創(chuàng)建庫
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）建庫時(shí)提示已存在
mysql> create database db1; ERROR 1007 (HY000): Can't 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）創(chuàng)建數(shù)據(jù)庫并指定字符集
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)#不指定校驗(yàn)規(guī)則默認(rèn)就是 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; # 查看數(shù)據(jù)庫使用字符集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.進(jìn)入一個(gè)庫 mysql> use db2#2.查看當(dāng)前所在庫 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）數(shù)據(jù)類型
int 整數(shù) -2^31 - 2^31-1 (-2147483648 - 2147483647) 不能超出此范圍 bigint 較大整數(shù)數(shù)據(jù)類型 （-2^63 - 2^63-1） 比int整數(shù)類型可用范圍廣 tinyint 最小整數(shù) -128 - 127 #年齡 0 - 255 varchar 字符類型（變長） #身份證 char 字符類型（定長） enum 枚舉類型 #給它固定選項(xiàng)，只能選則選擇項(xiàng)中的值 性別 datetime 時(shí)間類型 年月日時(shí)分秒 注意： 如果使用datetime不是為now()當(dāng)前時(shí)間，而是直接書寫時(shí)間，如2020-03-01，則需要在配置文件中添加此行信息sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 否則數(shù)據(jù)可能插入完成后，并不會顯示插入后的內(nèi)容，而是00-00-00.
4）數(shù)據(jù)類型測試
#int類型
mysql> create table tb1(id int); # 創(chuàng)建表名為tb1，字段名為id，類型為intmysql> insert tb1 values(1); # 在tb1表中插入對應(yīng)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.插入數(shù)據(jù)
mysql> insert into student values(1,'邱導(dǎo)',-18,'M',now());
Query OK, 1 row affected (0.00 sec)
6）建表數(shù)據(jù)屬性
not null： #非空 primary key： #主鍵（唯一且非空的） auto_increment： #自增（此列必須是：primary key或者unique key） unique key： #唯一鍵，單獨(dú)的唯一的 default： #默認(rèn)值 unsigned： #非負(fù)數(shù) comment： #注釋
7）加上屬性建表
#1.建表語句
create table students(
id int primary key auto_increment comment "學(xué)生id",
name varchar(12) not null comment "學(xué)生姓名",
age tinyint unsigned not null comment "學(xué)生年齡",
gender enum('M','F') default 'M' comment "學(xué)生性別",
cometime datetime default now() comment "入學(xué)時(shí)間");#2.查看建表語句
mysql> show create table students;
| students | CREATE TABLE `students` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '學(xué)生id',`name` varchar(12) NOT NULL COMMENT '學(xué)生姓名',`age` tinyint(3) unsigned NOT NULL COMMENT '學(xué)生年齡',`gender` enum('M','F') DEFAULT 'M' COMMENT '學(xué)生性別',`cometime` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入學(xué)時(shí)間',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)#3.插入數(shù)據(jù)
mysql> insert into students values(1,'qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#因?yàn)橹麈I相同無法插入
mysql> insert into students values(1,'qiudao',18,'M',now());
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
#應(yīng)該
mysql> insert into students values('2','qiudao',18,'M',now());
Query OK, 1 row affected (0.00 sec)
#主鍵已經(jīng)設(shè)置自增沒必要自己插入#正規(guī)插入數(shù)據(jù)的寫法
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.插入多個(gè)新字段 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)
三、復(fù)制表結(jié)構(gòu)
# 對數(shù)據(jù)庫的表結(jié)構(gòu)拷貝 mysql> create table ttt like student; # 創(chuàng)建一個(gè)新的表叫ttt，和student的表結(jié)構(gòu)一致，但不會將student的數(shù)據(jù)拷貝過來 Query OK, 0 rows affected (0.10 sec)# 對數(shù)據(jù)庫數(shù)據(jù)拷貝 mysql> insert into ttt select * from student; # 將查詢出來的結(jié)果，全部導(dǎo)入到ttt表中# 對導(dǎo)入數(shù)據(jù)篩選 mysql> insert into ttt select * from student where age > 22; # 對篩選結(jié)果導(dǎo)入到表ttt中