mysql基础小结

基础操作

# 选择数据库
show databases; 

# 创建temp库,utf8
create database temp character set utf8;

# 选择temp库
use temp;

# 创建students表
CREATE TABLE students (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, # 学生id，int整数型列，
    # unsigned无符号从零开始修饰符有符号是负数到正数
    # notnull不许为空，因为id后边要作为primary key，主键不可为空，如果不指定该属性，默认可为空
    # auto_increment自增长
    name CHAR(8) NOT NULL,# char
    sex CHAR(4) NOT NULL,
    age TINYINT UNSIGNED NOT NULL,
    tel CHAR(13) NULL DEFAULT '-' #默认值为空，不想为空指定为——
);

# 增加address列students
alter table students add address char(60); 

# age 列后插入
alter table students add birthday date after age;

# 删除birthday
alter table students drop birthday;

# tel列 重命名为 telphone
alter table students change tel telphone char(13) default "-";

# name列 数据类型修改为 char(16)
alter table students change `name` `name` char(16) not null; 

# 删除 birthday 列
alter table students drop birthday;

# 重命名 students 表为 workmates
alter table students rename workmates; 

# 查看表
show tables;

# 查看表信息
describe workmates;
describe students;
# 删除 students 表
drop table workmates;

# 删除 temp 库
drop database temp;

查询语句

# 选择数据库
use credit;

# 查询语句
SELECT * FROM user_info limit 20;

SELECT user_id,occupation FROM user_info limit 20;

SELECT * FROM user_info 
where sex=1 and (education=1 or marriage=1) 
limit 20;

# 插入语句
insert into user_info values ('A10','1','1','1','1','1');
insert into user_info(user_id,sex) values ('A10','1');

# 记录更新
set sql_safe_updates = 0;
update user_info set occupation='2',education = '2' 
where user_id = 'A10';

# 删除记录
delete from user_info where user_id = 'A10';
# delete from user_info; # 删除user_info表所有记录

# 排序
select * from user_info order by user_id;
select * from user_info order by user_id asc; # 升序排列
select * from user_info order by user_id desc; # 降序排列
select * from user_info order by sex desc,education desc;
select * from user_info where sex = 1 order by user_id;

# 分组
SELECT 
    user_id, AVG(trans_amount)
FROM
    bank_detail
GROUP BY user_id;

SELECT 
    user_id,trans_type, AVG(trans_amount) trans_amount_avg
FROM
    bank_detail
GROUP BY user_id,trans_type;

# 空值
select * from user_info where sex is null;
select * from user_info where sex is not null;

# 模糊匹配
select * from user_info where user_id like '278%'; # user_id以278开头 
select * from user_info where user_id like '%278%'; # user_id包含278 
select * from user_info where user_id like '%278'; # user_id以278结尾

# 正则匹配
SELECT * FROM user_info WHERE user_id REGEXP '^278'; # user_id以278开头
select * from user_info where user_id like '278%';

# 内连接
SELECT user_info.user_id,# 查询用户表里的
       sex,
       trans_amount
FROM user_info,bank_detail #两个表中都有要查询的字段
WHERE user_info.user_id = bank_detail.user_id;# 连接条件两张表上的用户ID保持一致

select count(*) from (
SELECT user_info.user_id,
       sex,
       trans_amount
FROM user_info,bank_detail 
WHERE user_info.user_id = bank_detail.user_id) a;

# 左连接
select count(*) from (
SELECT 
    user_info.user_id, sex, trans_amount
FROM
    user_info
        LEFT JOIN
    bank_detail ON user_info.user_id = bank_detail.user_id) a;

# 右连接
SELECT 
    user_info.user_id, sex, trans_amount
FROM
    user_info
        RIGHT JOIN
    bank_detail ON user_info.user_id = bank_detail.user_id;


# 自定义函数
DROP FUNCTION if exists standardize;

DELIMITER $
CREATE FUNCTION standardize(x float,y int) 
RETURNS float
BEGIN
DECLARE a float;
SET  a = x/5;
RETURN a+y;
END $
DELIMITER ;

select standardize(3.2,3);

SELECT 
    STANDARDIZE(trans_amount,1)
FROM
    bank_detail;

# 存储过程
drop procedure if exists info;

delimiter $ 
create procedure info(uid int) 
begin 
select * from user_info where user_id = uid; 
end $ 
delimiter ; 

call info(10);

# 重复数据
SELECT 
    user_id, COUNT(*) AS repetitions
FROM
    bank_detail
GROUP BY user_id
HAVING repetitions > 1;

# 过滤重复
select count(*) from
(select * from bank_detail
group by user_id,trans_amount) a;