2019.3.22 SQL语句(基础篇)
SQL语句
创建一个数据库：
create database+数据库名;
使用数据库：
use+数据库名;
查看mySQL中有哪些数据库：
show databases;
删除数据库
drop database + 数据库名;
创建表：
create table + 表名(列名+类型,….,);
查看表：
show tables;
查看表是如何创建的：
show create table +表名;
添加数据：
insert into + 表名 (列名,列名,…,列名) values(列名对应值);或
Insert into student values(直接按照建表时列名的顺序写);
查询表的内容：
select * from + 表名;
- 主键约束(primary key)
- 被标记为主键的列,数据不能重复
- 原则上一个表只能有一列被标记为主键
- 自增约束(auto_increment)
- 将该列设置为自增字段(相对于上一行+1),一般和主键放在一起设置
- 非空约束(not null)
- 被标记的列不能为空
- 唯一约束(unique)
- 被标记的列值不能重复 可以标记多行
增删改 这三个sql语句被称为DML(数据操作语句)
insert update delete
DDL: 数据定义语句
create drop alter
DQL:数据查询语句
select
DCL:数据控制语句
commit rollback
练习
select * from student;
create table if not exists teacher(
tid integer primary key auto_increment, //想自增必须是主键
name varchar(10) not null unique ,
address varchar(200),
gender varchar(5)
);
- insert 添加数据
insert into teacher
(name, address, gender)
values ('张三','大连','男');
insert into teacher
(name, address, gender)
values ('李四','大连','男'),
('王五','沈阳','女');
insert into teacher
values (null ,'赵六','大连','男');
更新数据
update teacher
set address = '北京'
where name = '赵六';
update teacher
set address = '天津',
gender = '女'
where name = '赵六' and address = '北京';
where name = '赵六' or address = '北京';
删除
delete from teacher
where name = '赵六';
查询
select *
from teacher;
结果只显示个别几列
select name,address
from teacher;
后面可以跟where子句
select name,gender
from teacher where tid > 2;
select * from teacher
where tid > 1 and gender = '女';
select *
from teacher
where tid > 2 or gender = '男';
where后面可以加order by排序
order by 列名 [排序方式]
select *
from teacher
where tid > 2 or gender = '男'
order by tid desc ;
limit+要显示的数量
limit 从第几行开始,显示多少行
select *
from teacher
where tid > 2 or gender = '男'
order by tid desc
limit 2,1;
create table Score
(sname varchar(20),cname varchar(50),grade int);
insert into Score
(sname, cname, grade)
values ('张三','语文',90),
('张三','数学',80),
('张三','英语',70),
('李四','语文',60),
('李四','数学',66),
('李四','英语',80),
('王五','语文',70),
('王五','物理',60);
update Score
set grade = 95
where cname = '语文' and sname = '张三';
select *
from Score
where sname = '李四';
delete from Score
where sname = '王五' and cname = '物理';
select sname,cname
from Score
where grade > 90;
select sname
from score
where grade < 60 and cname = '语文';
select grade
from score
where cname = '数学'
order by grade desc;
select sname,grade
from score
where cname = '英语'
order by grade desc
limit 5;
create table Employee
(eid int primary key auto_increment,
name varchar(20),
age int,
salary int,
depart varchar(50),
workage int);
insert into Employee
values (null,'张三',30,11000,'研发部',3),
(null,'李四',29,12000,'开发部',2),
(null,'王五',31,13000,'市场部',1),
(null,'赵六',28,14000,'人事部',1),
(null,'王七',32,15000,'研发部',5),
(null,'张八',27,9900,'开发部',4),
(null,'李九',33,9800,'市场部',3);
select *
from Employee;
update Employee
set salary = 20000
where eid = 2;
insert into Employee
values (null,'张三',30,11000,'研发部',3),
(null,'李四',29,12000,'开发部',2),
(null,'王五',31,13000,'市场部',1),
(null,'赵六',28,14000,'人事部',1),
(null,'王七',32,15000,'研发部',5),
(null,'张八',27,9900,'开发部',4),
(null,'李九',33,9800,'市场部',3);
select *
from Employee
limit 10,5
select name,salary
from Employee
where workage >= 5;
select *
from Employee
where eid > 10
order by age desc
select depart,salary
from Employee
where salary = (select max(salary) from Employee)
max组函数报错‘’In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'j0302.Employee.depart'; this is incompatible with sql_mode=only_full_group_by‘’
的解决方法：
先执行
select version(), @@sql_mode;z
再执行
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
