mysql学习

1、创建数据库

创建字符集为utf-8的数据库

create database blog default character set utf8;

进入blog数据库

use blog;

向blog数据库导入数据

source C:\Users\32171\Desktop\sql\blog.sql

或者

mysql –uroot –p123456 -Dblog<C:\Users\32171\Desktop\sql\blog.sql

mysql存储过程，插入一万条数据

DROP PROCEDURE IF EXISTS dept_initData1;
CREATE PROCEDURE dept_initData1()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
       INSERT INTO dept(deptName,salary,createDate,remark) VALUES(i,10000,now(),'test');
        SET i = i+1;
    END WHILE;
END
//　　

执行存储过程

delimiter ；
call dept_initData1();　　

删除存储过程

drop  procedure  dept_initData1;　　

去重 distinct

select distinct role from user_info 　　

 

 

 group by去重

https://www.jianshu.com/p/c92f312f8b40

内连接 inner join （默认）

select a.id as aid,a.user_name,a.card_id,b.name,b.id 
from user_info a,role b where a.role_id=b.id limit 0,2　　　　

使用谓词

select a.id as aid,a.user_name,a.sfz_id,b.name,b.id 
from user_info a INNER JOIN role b where a.role_id=b.id limit 0,2　　

子查询

select * from user_info where role_id
in (select id from role where name='admin')　　

分组

select role_id ,group_concat(user_name) as user_name from user_info group by role_id 　　

正则匹配user_name以博开头

select * from user_info  where user_name regexp '^博';　　

视图

create view view_user_info as select * from user_info

select * from view_user_info

alter view view_user_info as select id,user_name from user_info

select * from view_user_info

drop view  if exists view_user_info