MySql基础语句

MySQL基础语句个人笔记
CREATE TABLE USER(
user_id INT NOT NULL PRIMARY KEY,
user_name VARCHAR(20) NOT NULL,
user_sex CHAR(2)
)

SELECT * FROM USER
SELECT user_id,user_name FROM USER

//DISTINCT过滤重复值
SELECT DISTINCT user_name FROM USER
SELECT * FROM USER WHERE user_id = 5
SELECT * FROM USER WHERE user_id < 5

//and 意思可以理解为并/并且
SELECT * FROM USER WHERE user_id > 0 AND user_id < 4
SELECT * FROM USER WHERE user_id > 3 OR user_id > 1

//or意思可以理解为或/或者
SELECT * FROM USER WHERE NOT user_id >= 4

//is null 筛选搜索条件为空的
SELECT * FROM USER WHERE user_sex IS NULL

//is not null筛选搜索条件不为空的
SELECT * FROM USER WHERE user_sex IS NOT NULL

//搜索id区间2到6
SELECT * FROM USER WHERE user_id BETWEEN 2 AND 6

//搜索id在3，6，9的
SELECT * FROM USER WHERE user_id IN (3,6,9)

//模糊搜索中间字为“文”
SELECT * FROM USER WHERE user_name LIKE ‘%文%’

//0为false，不显示
SELECT user_name FROM USER WHERE 0

//1为true，显示
SELECT user_name FROM USER WHERE 1

//条件搜索，中间字为文并且性别不是空
SELECT * FROM USER WHERE user_name LIKE ‘%文’ AND user_sex IS NOT NULL

//条件搜索，中间字为文并且性别是男
SELECT * FROM USER WHERE user_name LIKE ‘%文’ AND user_sex = ‘男’

//条件搜索，中间字为文或者性别是男
SELECT * FROM USER WHERE user_name LIKE ‘%文’ OR user_sex = ‘男’

//条件搜索，区间2到8并且性别为女
SELECT * FROM USER WHERE user_id BETWEEN 2 AND 8 AND user_sex = ‘女’

//条件搜索，区间2到8 并且中间字为文 或者 中间字为四 并且 性别为女
SELECT * FROM USER WHERE user_id BETWEEN 2 AND 8 AND user_name LIKE ‘%文’ OR user_name LIKE ‘%四’ OR user_sex = ‘女’

SELECT * FROM USER WHERE price IS NULL
SELECT * FROM USER WHERE price > 30 AND (user_name LIKE ‘%文%’ OR user_name LIKE ‘%四%’) AND user_id > 2
SELECT price FROM USER ORDER BY price ASC
SELECT * FROM USER ORDER BY price ASC
SELECT * FROM USER WHERE user_id BETWEEN 2 AND 8 ORDER BY price DESC
SELECT * FROM USER WHERE (user_name LIKE ‘%文%’ OR user_name LIKE ‘%四%’) ORDER BY price DESC
SELECT user_id,user_name,price FROM USER WHERE user_name NOT LIKE ‘%文%’ AND user_name NOT LIKE ‘%四%’ ORDER BY user_id,price ASC

INSERT INTO USER VALUES(11,‘张飞’,‘男’,112)
INSERT INTO USER(user_id,user_name,price) VALUES(12,‘张伟’,82)
INSERT INTO USER VALUES (15,‘李太白’,‘男’,RAND()*100)

UPDATE USER SET user_sex = ‘女’ WHERE user_id = 12
UPDATE USER SET price = 149 WHERE user_id = 14
UPDATE USER SET user_name = ‘武则天’, user_sex = ‘女’, price = 132 WHERE user_id = 13

DELETE FROM USER WHERE user_id = 15
DELETE FROM USER WHERE user_name LIKE ‘李_白’
INSERT INTO USER VALUES(14,‘李白’,‘男’,178)

SELECT * FROM USER LIMIT 5
SELECT user_id FROM USER LIMIT 5
(SELECT * FROM USER LIMIT 5) ORDER BY price ASC
SELECT * FROM USER WHERE user_id <= 5 AND (user_name LIKE ‘%张%’ OR user_name LIKE ‘%李%’)

SELECT * FROM USER WHERE user_id LIKE ‘1_’
SELECT * FROM USER WHERE user_name LIKE ‘%李%’
SELECT * FROM USER WHERE user_id IN (2,4,6,8,10)
SELECT * FROM USER WHERE user_id BETWEEN 2 AND 6

SELECT user_id AS ‘编号’,user_name AS ‘姓名’,user_sex AS ‘性别’,price AS ‘战力’ FROM USER

CREATE TABLE emp(
id INT NOT NULL PRIMARY KEY,
name varchar(20) not null,
message varchar(1000)
)

select user.user_id,user.user_name,user.user_sex,user.price,emp.message from user inner join emp on user.user_id = emp.id
select user.user_id,user.user_name,user.user_sex,user.price,emp.message from user left join emp on user.user_id = emp.id
select user.user_id,user.user_name,user.user_sex,user.price,emp.message from user right join emp on user.user_id = emp.id
select user.user_id,user.user_name,user.user_sex,user.price,emp.message from user full outer join emp on user.user_id = emp.id
select emp.message from user full join emp on user.user_id = emp.id
select user.user_id,user.user_name,emp.message from user full order join emp on user.user_id = emp.id

select user.user_name from user union select emp.message from emp
select user_name from user union select message from emp

create table su as select * from user

insert into emp select * from su
create table su1 like su
insert into su1(user_id,user_name) select emp.id,emp.name from emp
insert into su1(user.user_id) select emp.id from emp
select su1.user_id from su1 union all select emp.message from emp
select su1.user_id,su1.user_name,emp.message from su1 inner join emp on su1.user_id = emp.id
insert into su1(user_id,user_name,price) select emp.id,emp.name,emp.id from emp

create index ins on user(user_id)
select user_id from user order by user_id asc
select ins
alter table su1 add num varchar(20)
create table su2 like su1
insert into su2 select * from su1
truncate table su2

create table su3(
ID int not null auto_increment primary key,
suName varchar(20) not null
)
select * from su3
insert into su3(suName) values(‘fjd’)
insert into su3(suName) values (‘张三’)

create view view1 as select user_id from user
select * from view1 as id
create view a1 as select * from user
select a1.user_id as id, a1.user_name as ‘姓名’ from a1
//创建双表视图
create view a2 as select user.*,emp.message from user left join emp on user.user_id = emp.id
//创建多表视图
create view a3 as select su2.user_id,user.user_name,emp.message from (user left join emp on user.user_id = emp.id)left join su2 on su2.user_id = user.user_id

select * from a2
select * from a3
SELECT * FROM USER ;
SELECT * FROM emp;
SELECT * FROM su;
SELECT * FROM su1;
SELECT * FROM su2;

select * from user limit(8)*3,9
select user.user_id,user.user_name,emp.message,su1.num from (user left join emp on user.user_id = emp.id) left join su1 on user.user_id = su1.user_id
select user.user_id,user.user_name,emp.message from user inner join emp on user.user_id=emp.id

select a.user_id,a.user_name from user a