sql语句操作大全

#查看数据库:

show databases;

#创建数据库：mysql的utf8是三个字节的阉割版utf8编码，utf8mb4是完整的utf8编码

create database if not exists db1 default charset utf8mb4;

#进入db1数据库

use db1;

#查看数据库

show databases;

show create database db1;

#修改数据库参数

alter database db1 charset utf8;

show create database db1;

alter database db1 charset utf8mb4;

show create database db1;

#删除数据库

drop database db1;

表的操作

#创建表

#数据存储引擎：

#innodb - 支持外键、事务、行锁

#myisam - 不支持外键、事务、表索，只读表可以选择用myisam,效率高

create table tb1(

id int primary key auto_increment,

name varchar(40) not null

) engine=innodb charset=utf8mb4;

#修改表的属性

alter table tb1 charset=utf8;

#添加字段，alter修改表

alter table tb1 add username varchar(40) unique after id;

#查看表结构

desc tb1;

# name改成nickname

alter table tb1 change name nickname varchar(40);

#修改字段数据类型

# varchar(10)

#char(10)

alter table tb1 add gender varchar(10);

alter table tb1 modify gender char(1);

#删除字段

alter table tb1 add birthdate date;

alter table tb1 drop birthdate;

#truncate 截断、重建、重置

#先删除表然后自动重建表

insert into tb1(username) values('aa'),('bb'),('cc');

select *from tb1;

show create table tb1;

truncate table tb1;

#删除表

drop table if exists tb1;

###用户管理

'''sql

#mysql用户有两个字段：用户名、登录的主机地址：

root       'localhost'          'root'
root      '192.168.0.5'         'abc123'
root      '192.168.9.%'         '123456'
root      '%'                   '111111'

#默认只有root@localhost,只允许本机登录

#添加允许远程登录的用户

create user 'root'@'%' identified by 'root';

#查询系统用户表

select user, host from mysql.user;

#用户授权

grant   select,insert,update,delete    on    test.*   to  'abc'@'%‘

grant  create,alter, drop    on   db1.*   to   'abc'@'localhost'

grant  all privileges   on    *.*      to   'abc'@'%'

grant   select,insert   on    db1.t1   to  'abc'@'%'

grant select(id, name)    on   db1.t1   to  'abc'@'%'

grant all privileges on   *.*   to 'root'@'%';

查询测试

#查看表结构

desc employees;

#查询员工数据

limit 10 查询前10条，相当于limit0,10

limit  10,10 查询从第11条开始的10条数据

limit 20,10 查询从第21条数据开始的10条

limit 分页，越往后越慢

select*from employees limit 10;

select*from employees limit 29990,10;

#查询员工id,first_name,salary

explain

select e.emp_no,e.first_name,s.salary

from employees e

join salaries s on e.emp_no=s.emp_no

where e.emp_no=1001;

#查询title在2002年变为‘Engineer'的员工

explain

select e.emp_no,e.first_name,t.title, t.from_date

from employees e

join titles t on e.emp_no=t.emp_no

where t.title='Engineer' and t.from_date>='2002-1-1';

#查询dept_no是d007的员工

explain

select e.emp_no,e.first_name,d.dept_no

from employees e

join dept_emp d on e.emp_no=d.emp_no

join departments p on d.dept_no=p.dept_no

where p.dept_no='d007'

limit 10;

#查询部门d007的经理id

explain

select e.emp_no,e.first_name,m.dept_no

from employees e

join dept_manager m on e.emp_no=m.emp_no

where m.dept_no='d007';

#工资salary小于等于39000,大于等于150000

explain
select *
from salaries 
where salary<=39000
and from_date>='2002-1-1'

explain
select *
from salaries 
where salary>=150000
and from_date>='2002-1-1'

使用索引查询

--按first_name来过滤查询员工

--加索引前，235毫秒

--加索引后，0到16毫秒

explain

select*from employees

where first_name='Georgi';

--添加first_name的索引

create index employees_fn_index on employees(first_name);

--查看索引

show index from employees;

--删除索引

drop index frist_name on employees;