MySQL语句大全
进入/登录数据库:mysql -uroot -p密码
数据库退出:exit
查看数据库版本:select version();
显示当前时间:select now();
数据库操作
查看所有数据库:show databases;
创建数据库:create database 数据库名 charset=utf8;
(默认采用utf8编码)
删除数据库:drop 数据库名;
切换数据库:use 数据库名;
查看当前正在使用的数据库:select database();
查看当前数据库中的表:show tables;
数据库备份及恢复
备份数据库中所有表的数据
mysqldump -u用户名 -p [数据库名] > 文件名.sql
备份数据库中某个表的数据
mysqldump -u用户名 -p [数据库名] [数据表名] >文件名.sql
数据库恢复
第一步: 先创建新的数据库
第二步:mysql -u用户名 -p 新数据库名 < 文件名.sql
在cmd命令行中,在指定路径下运行备份的命令语句,备份文件就保存在该文件下,如下图;
恢复备份数据库:
下面看实际效果:
数据表操作
创建表
create table 表名(
id int unsigned auto_increment primary key not null,
name varchar(20) not null.
gender bit(1) not null default 1
);
create table 表名(
字段名 类型(长度)[约束],
字段名 类型(长度)[约束],
...);
建表主要是前面的字段,字段后面跟的是约束
例如,创建学生表:
create table students(
id int suto_increment primary key not null comment "主键",
name varchar(10) not null,
gender bit(1) default 0,
hometown varchar(40) default "" comment "家乡"
);
comment后面跟的是注释,解释该字段的含义
补充:
如果表存在,则不进行任何操作,如果不存在,则创建:
create table if not exists students(
id int suto_increment primary key not null comment "主键",
name varchar(10) not null,
gender bit(1) default 0,
hometown varchar(40) default "" comment "家乡"
);
以下为实操代码:
create table student2(
-> id int auto_increment primary key,
-> name varchar(20) not null,
-> age int(3));
CREATE TABLE student(
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` int(3) DEFAULT NULL,
`gender` bit(1) DEFAULT NULL,
`height` decimal(3,0) DEFAULT NULL,
`tel` char(11) DEFAULT NULL,
PRIMARY KEY (`id`)
);
查看创建表的sql语句
show create table 表名;
查看表结构
desc 表名
删除表
drop table 表名;
修改表
- 修改表名
rename table 表名 to 新表名
- 添加字段
alter table 表名 add 列名 类型;
给students 添加一个生日字段
alter table students add birthday date;
- 修改字段
第一种:不修改字段名,只修改类型及约束
alter table 表名 modify 列名 类型及约束;
例如:
alter table students modify hometown varchar(40) default null;
第二种:需要修改字段名字
alter table 表名 change gender sex bit(1) not null default b'1';
- 删除字段:
alter table 表名 drop 字段名;
插入数据:
- 全插入:
字段和值必须对应,个数相同,类型相同。
如果插入空值,可以不写,或者插入null。
insert into [table_name] values([value1, ...]);
insert into 表名 values (value1, value2,……);
例如:
inert into students values(1,"小王子","1","四川");
insert into student value(3,'xiaowangzi',18,1,1.78,'12345678911');
- 部分插入
insert into [table_name]([column1, ...]) values([value1, ...]);
insert into table (字段1,字段2,……) values (value1,value2,……);
例如:
insert into students (name,gender) values("xiaowangzi","0");
- 一次性插入多条
insert into [table_name] values([value1, ...]), ([value2, ...]), ...;
insert into [table_name]([column1, ...]) values([value1, ...]), ([value2, ...]), ...;
insert into students (name,gender) values('xiaochen','1'),('xiaowang','0');
删除数据:
delete from [table_name] where [condition];
-
删除id大于等于5的数据:
delete from 表名 where id >=5;
修改数据:
update [table_name] set [column1 = value1, ...] where [condition];
update 表名 set 字段名=新值 where 条件;
update students set name="xiaozi" where id=2;
查找数据:
该部分涉及较多,分点讲述
-
单表查询
-
查
查询表中所有数据:select * from 表名; 查询某列/几列所有内容: select [column1], [column2]... from [table_name]; 查询时,指定显示的别名: select [column1] as [alias1], [column2] as [alias2]... from [table_name];
-
去重
select distinct [column1],[column2]...from [table_name]
-
条件
select * from [table_name] where [condition];
这里补充一下运算符等条件查询所需的条件判断符号:
-
关系运算符:
=(等于)、!=或<>(不等于)、>(大于)、<(小于)、 >=(大于等于)、<=(小于等于)
-
逻辑运算符
and(与)、or(或)、not(非)
-
区间查询
between…and… 在某个范围内 in(…) 多个可能值
-
模糊查询
like… %匹配任意字符,_匹配1个字符 rlike… 匹配正则表达式
-
判空
is null/ is not null
-
-
聚合
select aggr_func([co,umn]) from [table_name] where [condition]; aggr_func:count、max、min、sum、avg select count([column]) from [tanle_name];等价于 select count(*) from [table_name];
-
分组
select [column] as [alias], count(*) from [table_name] where [condition] group by [column];
-
筛选
select * from [table_name] where [condition] group by [column] having [condition];
-
排序
select * from [table_name] order by [column1] asc/desc,[column2] asc/desc,…;
-
分页
select * from [table_name] limit[skip],[count];
-
-
子查询
-
标量子查询:返回一列一行的查询结果
例如: select id from dataset where height=(select max(height) from dataset);
-
列级子查询:返回一列多行的查询结果
例: select * from dataset where id in (select id from dataset where age < 30);
-
行级子查询:返回多列一行的查询结果
-
表级子查询:返回多列多行的查询结果
例: select * from (select id,age from [table_name]) as t;
-
-
联表查询
-
内连接
select * from [table_name1] inner join [table_name2] on [table_name1].[column1]=[table_name2].[column2];
-
左连接
select * from [table_name1] left join [table_name2] on [table_name1].[column1]=[table_name2].[column2];
-
右连接
select * from [table_name1] right join [table_name2] on [table_name1].[column1]=[table_name2].[column2];
-
-
并表
-
不去重
select * from [table_name1] union all select * from [table_name2];
-
去重
select * from [table_name1] union select * from [table_name2]
-
数据准备
这里给大家准备一些数据,用作练习使用,按照下面步骤进行,即可创建数据。
表1:student1
```mysql
登录:mysql -uroot -p密码
创建一个数据库:create database test charset=utf8;
选择刚刚创建的数据库:use test;
drop table if exists student1;
创建表:
CREATE TABLE `student1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` text,
`gender` bigint(20) DEFAULT NULL,
`birth` text,
`age` bigint(20) DEFAULT NULL,
`height` double DEFAULT NULL,
`tel` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`) );
插入数据:
INSERT INTO `student1` VALUES (1,'Thor Odinson',NULL,'1982-6-13',30,1.67,13958532741),(2,'Black Panther',1,'1983-8-26',32,1.63,15634732773),(3,'Vision',0,'1983-2-16',35,1.59,15919989799),(4,'Scarlet Witch',0,'1981-1-29',35,1.82,15120102156),(5,'Wasp',1,'1986-7-18',30,1.5,15649958060),(6,'Black Panther',1,'2000-7-6',29,1.69,15091026092),(7,'Winter Soldier',1,'1983-8-6',39,1.52,15234642699),(8,'Ant Man',0,'1979-11-29',31,1.48,18687085254),(9,'Iron Man',0,'1978-12-23',36,1.69,15996291703),(10,'Black Panther',0,'',36,1.59,13522109067),(11,'Ant Man',0,'1990-6-10',43,1.65,13366236395),(12,'Black Panther',1,'1978-5-22',28,1.68,13814279976),(13,'Captain Marvel',0,'1978-1-20',24,1.81,15112402176),(14,'Captain America',0,'1980-11-26',39,1.6,18756876020),(15,'Thor Odinson',1,'1985-7-23',40,1.8,15852158388),(16,'Thor Odinson',1,'1981-2-3',25,1.68,14715564684),(17,'Wasp',1,'1989-6-10',36,NULL,18598377083),(18,'Captain Marvel',0,'1995-10-23',30,1.79,18774246120),(19,'Wasp',1,'1986-12-28',33,1.78,13064806852),(20,'Wasp',0,'1994-2-15',38,1.69,13412871109),(21,'Thor Odinson',0,'1982-6-13',30,1.67,13958532741),(22,'Black Panther',1,'1983-8-26',32,1.63,15634732773),(23,'Vision',0,'1983-2-16',35,1.59,15919989799),(24,'Scarlet Witch',0,'1981-1-29',35,1.82,15120102156),(25,'Wasp',1,'1986-7-18',30,1.5,15649958060),(26,'Black Panther',1,'2000-7-6',29,1.69,15091026092),(27,'Winter Soldier',1,'1983-8-6',39,1.52,15234642699),(28,'Ant Man',0,'1979-11-29',31,1.48,18687085254),(29,'Iron Man',0,'1978-12-23',36,1.69,15996291703),(30,'Black Panther',0,'1993-11-10',36,1.59,13522109067),(31,'Ant Man',0,'1990-6-10',43,1.65,13366236395),(32,'Black Panther',1,'1978-5-22',28,1.68,13814279976),(33,'Captain Marvel',0,'1978-1-20',24,1.81,15112402176),(34,'Captain America',0,'1980-11-26',39,1.6,18756876020),(35,'Thor Odinson',1,'1985-7-23',40,1.8,15852158388),(36,'Thor Odinson',1,'1981-2-3',25,1.68,14715564684),(37,'Wasp',1,'1989-6-10',36,1.65,18598377083),(38,'Captain Marvel',0,'1995-10-23',30,1.79,18774246120),(39,'Wasp',1,'1986-12-28',33,1.78,13064806852),(40,'Wasp',0,'1994-2-15',38,1.69,13412871109);
```
表二:product:
```mysql
CREATE TABLE product(
pid INT PRIMARY KEY,
pname VARCHAR(20),
price DOUBLE,
category_id VARCHAR(32));
INSERT INTO product(pid, pname, price, category_id)VALUES
(1, '联想', 5000, 'c001'),
(2, '海尔', 4000, 'c001'),
(3, '惠普', 6000, 'c001'),
(4, '真维斯', 200, 'c002'),
(5, '香奈儿', 51000, 'c002'),
(6, '迪奥', 65000, 'c002'),
(7, 'mac', 300, 'c003'),
(8, '相宜本草', 200, 'c003'),
(9, '面霸', 5, 'c003'),
(10, '香飘飘', 1, 'c004'),
(11, '优乐美', 1, 'c004');
```