SQL语句
目录
SQL语句
一、基本SQL语句
1.查看所有的数据库名称(mysql的data下的文件夹)
show databases; # 查看所有数据库，一个名字就是一个库
2.查看当前库所有的表名称
show tables;
3.查看所有的记录
select * from mysql.user;
select * from user; # 如果在mysql库下，就直接写
select * from mysql.user/G; # 格式化展示，竖向形式
4. 查看当前在哪个库下
select database();
5. SQL注释
-- 单行注释
#单行注释
/*多行注释*/
6. 给字段添加注释comment
cerate table t1(id int primary key auto_increment,name varchar(32) comment '这是名字注释');
'''
sql语句必须以分号结尾
sql语句编写错误之后不用担心，
可以直接执行,直接报错
或者取消执行，在语句后面加\c
cmd中粘贴直接使用右键
MySQL默认忽略大小写
'''
查看
# 查看所有的数据库名称
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
'''
information_schema,是虚拟库，保存在内存中的，
mysql、performance_schema、test，是保存在硬盘中的库
'''
# 查看字段注释
mysql> create table t1(id int primary key auto_increment,name varchar(32) comment '这是名字注释');
Query OK, 0 rows affected (0.05 sec)
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) DEFAULT NULL COMMENT '这是名字注释',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
二、针对库的sql语句
'''基于库的增删改查'''
# mysql自带的库，不要对其进行操作
1.创建库/增加库
create database 库名;
2.查看库
show databases; 查看所有库
show create database 库名; 查看指定库信息
3.编辑库/修改库 # 一般很少用，甚至于不用
alter database 库名 charset='gbk' 修改库的字符编码格式
4. 删除库
drop database 库名;
创建库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
'''
information_schema,是虚拟库，保存在内存中的，
mysql、performance_schema、test，是保存在硬盘中的库
'''
# 创建库db1:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
# 出现OK，就表示这条语句执行成功了
查看库
1.查看所有库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
'''
db1,是刚刚创建好的数据库
'''
2.查看指定库信息
mysql> show create database db1;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
修改库
alter database 库名 charset='gbk' 修改库的字符编码格式
mysql> alter database db1 charset='gbk';
Query OK, 1 row affected (0.00 sec)
mysql> show create database db1;
+----------+-------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET gbk */ |
+----------+-------------------------------------------------------------+
1 row in set (0.00 sec)
# 最后的字符编码由latin1修改成了gbk
删除库
drop database 库名;
mysql> drop database db1;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
# db1被删除了
三、针对表的sql语句
'''基于表的增删改查'''
操作表之前需要先确定库
create database 库名;
切换操作库
use 库名;
1.创建表/增加表
create table 表名(字段名1 字段类型,字段名2 字段类型);
2. 查看表
show tables; 查看库下的所有表名称
show create table 表名; 查看指定表信息 # 使用频率不高
describe 表名; 查看表结构
简写：desc 表名;
ps:如果想跨库操作其他表 只需要在表名前加库名即可
desc mysql.user;
3. 编辑表/修改表
alter table 表名 rename 新表名; 修改表名
4. 删除表
drop table 表名;
删除的操作区别
delete t9; 删除表数据，表还在
truncate t9; 清空表数据并重置主键，表还在
DROP DATABASE 数据库名; 删除数据库
先切换操作库
mysql> use db1;
Database changed # 此时的数据库就是db1
创建表
# 创建表t1:
mysql> create table t1(id int, name varchar(16));
Query OK, 0 rows affected (0.04 sec)
# 创建表t2:
mysql> create table t2(id int,name varchar(16),age int,gender varchar(16) );
Query OK, 0 rows affected (0.05 sec)
查看表
1.查看库下的所有表名称
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
+---------------+
2 rows in set (0.00 sec)
2.查看指定表信息
输出的是创建表的完整语法结构
mysql> show create table t1;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.查看表结构
mysql> describe t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
# t1表内有两个字段名
# 可以简写为：
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(16) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)
4.如果想跨库操作其他表 只需要在表名前加库名即可
# 查看库db2下的表ta1的结构
mysql> desc db2.ta1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.03 sec)
修改表
mysql> use db2;
Database changed
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| ta1 |
+---------------+
1 row in set (0.00 sec)
# 修改表名
mysql> alter table ta1 rename ta2;
Query OK, 0 rows affected (0.02 sec)
mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| ta2 | # 表名被改为ta2
+---------------+
1 row in set (0.00 sec)
删除表
# 删除表
mysql> drop table ta2;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)
四、针对记录的sql语句
'''基于记录的增删改查'''
1.添加记录/插入记录
# 是全字段增加，所有字段都要有数据
insert into 表名 values(值1, 值2, 值3); 单条记录
insert into 表名 values(值1, 值2, 值3),(值1, 值2, 值3),(值1, 值2, 值3),(值1, 值2, 值3); 多条记录
# 部分字段添加数据，需要在表名后面指定字段名
insert into 表名(字段1, 字段2) values(值1, 值2); 单条记录
insert into 表名(字段1, 字段2) values(值1, 值2),(值1, 值2); 多条记录
2.查询记录
select * from 表名; 查询表中所有的记录
select 字段 from 表名; 查询表中单字段的记录
select 字段1，字段2 from 表名; 查询表中多个字段的记录
3.编辑记录
update 表名 set 字段名=新数据 where 筛选条件; 单个约束条件
update 表名 set 字段=值 where 约束条件1 and 约束条件2; 多个约束条件，不同约束条件之间用and连接
update 表名 set 字段1=值,字段2=值,字段3=值 where 约束条件; 修改多个值，不同值之间用逗号隔开
update 表名set name='tank' 没有约束条件，修改全表中的name
4.删除记录
delete from 表名; 删除整个表的所有数据
delete from 表名 where id=2; 只删除id=2的这行数据
添加记录/插入记录
1.全字段增加单条数据
mysql> insert into t1 values(1, 'kevin');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
+------+-------+
1 row in set (0.00 sec)
2.全字段增加多条数据
mysql> insert into t1 values(2,'jack'),(3,'tom'),(4,'jack'),(5,'tory');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
+------+-------+
5 rows in set (0.00 sec)
3.部分字段添加单条数据
mysql> insert into t1(id) values(6);
Query OK, 1 row affected (0.02 sec)
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
| 6 | NULL |
+------+-------+
6 rows in set (0.00 sec)
4.部分字段添加多条数据
mysql> insert into t1(name) values('ke1'),('ke2');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
| 6 | NULL |
| NULL | ke1 |
| NULL | ke2 |
+------+-------+
8 rows in set (0.00 sec)
查询记录
1.查询表中所有的数据
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
| 6 | NULL |
| NULL | ke1 |
| NULL | ke2 |
+------+-------+
8 rows in set (0.00 sec)
2.查询表中单字段的数据
mysql> select name from t1;
+-------+
| name |
+-------+
| kevin |
| jack |
| tom |
| jack |
| tory |
| NULL |
| ke1 |
| ke2 |
+-------+
8 rows in set (0.00 sec)
3.查询表中不同字段的数据
mysql> select id,name from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
| 6 | NULL |
| NULL | ke1 |
| NULL | ke2 |
+------+-------+
8 rows in set (0.00 sec)
编辑记录
1.单个约束条件
mysql> select * from t1;
+------+-------+
| id | name |
+------+-------+
| 1 | kevin |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
+------+-------+
5 rows in set (0.00 sec)
mysql> update t1 set name='k' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | k |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 5 | tory |
+------+------+
5 rows in set (0.00 sec)
2.多个约束条件
不同约束条件之间用and连接
mysql> update t1 set id=1 where name='tory' and id=5;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 1 | k |
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 1 | tory | # 这条记录改变了
+------+------+
5 rows in set (0.00 sec)
3.修改多个值
mysql> update t1 set id=6,name='name' where id=1;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0 # 有两条记录改变了
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 6 | name | # 这条记录改变了
| 2 | jack |
| 3 | tom |
| 4 | jack |
| 6 | name | # 这条记录改变了
+------+------+
5 rows in set (0.00 sec)
4.没有约束条件，会修改符合条件的所有值
工作中要避免这种情况的出现。
mysql> update t1 set name='kkk';
Query OK, 5 rows affected (0.01 sec)
Rows matched: 5 Changed: 5 Warnings: 0 # 所有记录都改变了
mysql> select * from t1;
+------+------+
| id | name | # 所有记录都改变了
+------+------+
| 6 | kkk |
| 2 | kkk |
| 3 | kkk |
| 4 | kkk |
| 6 | kkk |
+------+------+
5 rows in set (0.00 sec)
删除记录
1.删除符合约束条件的这行数据
mysql> delete from t1 where id=6;
Query OK, 2 rows affected (0.00 sec) # 删除了两条记录
mysql> select * from t1;
+------+------+
| id | name |
+------+------+
| 2 | kkk |
| 3 | kkk |
| 4 | kkk |
+------+------+
3 rows in set (0.00 sec) # 剩下了3条记录
2.删除整个表的所有数据
mysql> delete from t1;
Query OK, 3 rows affected (0.01 sec) # 删除了表中的所有数据
mysql> select * from t1;
Empty set (0.00 sec) # 此时表为空
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 | # 使用delete只会删除表内的数据，但是表t1还在
| t2 |
+---------------+
2 rows in set (0.00 sec)
