数据库操作SQL命令

1.关于数据库的操作

• 1.1创建数据库(注：sql语句区分大小写)
  create database Hero
• 1.2使用某个数据库
  use Hero
go--执行
• 1.3删除数据库
  drop database Hero

2.关于表的操作

• 2.1创建表并指定主键
  create table hero
  (heroID int primary key,heroName varchar(50)
  )
• 2.2删除表
  drop table hero
• 2.3显示表操作
  SHOW CREATE TABLE 表名

3.关于表数据的操作

• 3.1添加数据
  insert into hero values(1,'宋江')

• 3.2查询数据
  select * from hero where heroName='宋江'

• 3.3修改数据
  update hero set heroID=heroID*1.1,heroName='武松'

• 3.4删除数据
  delete from hero where heroID=1 and heroName='武松'

• 3.5插入部分字段(主键必须给)
  insert into hero (heroID) values(1)

• 3.6外键的介绍

  即建立关联：（）外键只能指向主键
  （）主键和外键的数据类型要一致

  create table emp( empo int primary key, deptno int foreign key references dept(deptno) )
  注：dept为表(deptno为字段)

• 3.7查询指定列
  select 字段，字段from 表名where 条件

• 3.8取消重复行
  select distinct 字段from 表名where 条件

• 3.9取消外键
  CONSTRAINT 'prod_country' FOREIGN KEY ('country_id') REFERENCES 'country'('id') # 外键约束
  ALTER TABLE 表名 DROP FOREIGN KEY prod_country; # 去除外键约束

4.关于数据技巧的操作

• 4.1加别名
  select 字段别名，字段from 表名where 条件
• 4.2判断是否为NULL,若为NULL则用代替
  select 字段，isnull(字段,0) from 表名where 条件
• 4.3判断是否在两者之间，包括两者（数据型）
  select * from 表名where sal between 2000 and 5000
• 4.4显示首字母为s的人% ：代表到多个字符_ :代表单个字符
  select * from hero where heroName like's%'
• 4.5 or 和in 的区别
  select * from hero where heroID=1 or heroID=2 or heroID=5
select * from hero where heroID in(1,2,5)
• 4.6升降序的排列
  select * from hero order by heroID desc--降序
select * from hero order by heroID-------默认为升序（asc）
• 4.7聚合函数如min()
• 4.8平均和求和
  select avg(sal) ,sum(sal) from hero
• 4.9计算有多少记录
  select count(*) from hero

5.关于数据复杂查询

• 5.1group by分组，having对分组结果进行筛选
  select avg(sal) from hero group by 部门having avg(sal)>2000
• 5.2取了别名后,别名先载入,而且以后也得用别名
  select e.dept from emp e,depo d where e.dept=d.dept
• 5.3单行子查询= 多行用in
  select dept from emp where dept=(select dept from emp where name='song')
select dept from emp where dept in(select dept from emp where name='song')
• 5.4显示前个人的记录
  select top 4 *from emp order by sal
  • 显示-10人的记录
    select top 6 *from emp not in(select top 4 *from emp order by sal) order by sal
• 5.5自增长和构造大量数据

  create table hero
  (heroID int primary key identity(1,1),heroName varchar(50)
  )--identity(1,1)表示该字段自增从，每次+1
  insert into hero (heroID) values(1)
  insert into hero(heroID) select heroID from hero
  

• 5.6左外连右外连
  select w.ename,b.ename from emp w left join emp b on w.mgr=b.empo

6.关于数据的约束

• 6.1 not null unique check default

create table hero
(heroID int primary key identity(1,1) not null,--非空
heroName varchar(50) unique------------------唯一
sal int check sal>2000------------------------规定取值范围
sex nchar(1) check(sex in('男'，'女')) default '男'----------------------默认)

7.关于数据库备份和还原

backup database Hero to disk='f:/sp.bak'---备份
restore database Hero from disk='f:/sp.bak'--还原

8.关于数据库的测试

• 自我复制
  insert into users (username,passwd,email,grade)
select username,passwd,email,grade from users

9.关于sql语句的其它操作

• 查询数据库
  show databases;
• 查询表名
  show tables;
• 查询表字段
  show columns from 表名
• 格式化查询结果并限制数量
  select * from table_name limit 10\G

10. mysql权限相关

• 创建用户

CREATE USER 'test'@'%' IDENTIFIED BY 'test@123';
'%' - 所有情况都能访问
'localhost' - 本机才能访问
'111.222.33.44' - 指定 ip 才能访问

• 赋予权限

grant all on 数据库名.数据库表 to 用户名@'%'  IDENTIFIED by '密码';
all 可以替换为 select,delete,update,create,drop
数据库名 所有的 用*
数据库表 所有的 用*