MYSQL 笔记（一）

数据库概念

数据库：是按照数据结构进行组织、管理和储存得仓库

关系型数据库：通过关系模型组织数据得仓库

特点：易于理解，易于维护

DCL        数据库操控语言

DDL        数据定义语言

DML        数据操作语言

DQL        数据查询语言

数据库操控语句

-- 创建一个新的用户
-- CREATE USER aa@localhost IDENTIFIED BY '123456';
CREATE USER mary@localhost IDENTIFIED by '123123';

-- 为新用户进行授权
GRANT ALL ON test.* to mary@localhost;

-- 创建用户的同时进行授权
GRANT SELECT ON test.* to b@localhost IDENTIFIED BY '111111';

-- 显示某个用户的权限
sHOW GRANTS FOR mary@localhost

sHOW GRANTS FOR b@localhost

-- 回收权限
REVOKE delete on test.* from mary@localhost

数据定义语句

-- 显示所有的数据库
show DATABASES;

-- 创建一个新的数据库
CREATE DATABASE ck_001   -- Can't create database 'ck_001'; database exists
--  创建数据库并判断是否存在 如果存在不创建  不存在时则创建
CREATE DATABASE if not exists ck_001
CREATE DATABASE if not exists ck_002

-- 删除数据库 同时可以进行判断 如果存在则可以删除  如果不存在则不进行删除
DROP DATABASE if EXISTS ck_001 

-- 使用数据库
use ck_001 

-- 显示某数据库中的所有表
SHOW TABLES;

-- 创建表
CREATE TABLE if not EXISTS admin (id int auto_increment primary key not null,name 
varchar(10),age int,gender varchar(1))

create table if not exists a like admin;

-- 删除表
drop table a

-- 显示表结构
desc ad

-- 修改表结构
-- 新增字段
alter table admin RENAME ad

-- 插入字段
alter table ad add phone varchar(11)

alter table ad add email varchar(20) first

alter table ad add address varchar(20) after gender 

-- 删除字段
alter table ad drop email

-- 改字段类型
alter table ad modify phone int             -- modify 只能改类型
alter table ad change phone tel varchar(11) -- change 改名，改类型

 数据操作语言

-- 添加
insert into admin (name,age,gender) values ('小明'，12,'男')
insert into admin values (default,'小明',12,'男','长春','123')
insert into admin values (null,'小明',12,'男','长春','123')
insert into admin (name,age) values ('小红',13)
-- 修改
update admin set gender=null where id=8
update admin set name='大名',age=23,gender='男' where id=2
-- 删除
delete from admin where id=3;

数据查询语言

-- 查询表中所有数据
SELECT id as 序号,name as 姓名,age 年龄,gender 性别,address 地址,tel 电话 FROM admin

SELECT id as 序号,name as 姓名,age 年龄,gender 性别,address 地址,tel 电话 FROM admin WHERE name='小明'

SELECT * FROM admin WHERE name='小明'

SELECT * FROM admin WHERE name='小明' and gender='女'

SELECT * FROM admin WHERE address='上海' or address='吉林'

SELECT * FROM admin WHERE id=6

SELECT * FROM admin WHERE age>=15 and age <=30

SELECT * FROM admin WHERE age between 15 and 30

SELECT * FROM admin WHERE age<=20 or age >=30

SELECT * FROM admin WHERE age!=24

SELECT * FROM admin WHERE gender is not null

SELECT * FROM admin WHERE gender is null

SELECT * FROM admin WHERE age in (15,30)
-- 注意括号表示集合，括号内的数据是集合中的元素

-- 升序排列
SELECT * from admin order by age asc
-- 降序排列
SELECT * from admin order by age desc

SELECT * from admin order by age desc limit 3

SELECT * from admin order by age desc

SELECT * from admin limit 0,3
SELECT * from admin limit 3,3 
-- 参数1：表示起始数据的下标  参数2：表示每次显示的个数
select count(gender) from admin
select count(id) from admin
select count(*) from admin
select sum(age) from admin
select max(age) from admin
select min(age) from admin
select avg(age) from admin


select gender,count(*) from admin group by gender
-- where 关键字是对原始表起作用
-- having 是对结果表  虚拟表起作用  二次筛选
select gender,avg(age) from admin group by gender
having avg(age) >= 20

select * from admin where address like '%朝阳区%'

select * from admin where name like '张%'
-- %表示任意字符
select * from admin where name like '%张%'
-- _表示单个字符
select * from admin where name like '张__'

select distinct gender from admin
-- 联合查询
select * from admin 
UNION all
select * from teacher 
-- 左连接
select * from admin left join teacher
on admin.id = teacher.id

-- 右连接
select * from admin right join teacher
on admin.id = teacher.id

-- 内连接
select * from admin inner join teacher
on admin.id = teacher.id

select * from player,team where 
player.tid = team.id and team.name = '中国队'
-- 子查询
select * from player where tid = (select id from team where name = '中国队')