sql基本操作

数据库有三层结构，库---》表---》数据（元组）
cmd中打开mysql的操作：mysql -u root -p


1.show databases;------》展示当前所有存在的数据库
2.create database 库名;------》创建数据库
3.show create database------》查看数据库定义
4.drop database 库名------》删除数据库（当使用该方法，数据库中结构和内容都会被删除而且不能恢复，所以使用的时候要小心）
5.SHOW ENGINES------》查看系统所支持的引擎类型（yes代表可以使用，no代表不能使用，default代表当前默认引擎，另外INNOdb是最为推荐的引擎，它的功能效果都比较优越）
6.在cmd中操作mysql的登录语言------mysql -h localhost -u root -p
7.create table 表名(            ------》创建表语句，表的约束语句有：not null（非空约束）,primary key（主键约束）,foreign key（外键约束）,check（检查约束）,default（默认约束）,unique（唯一约束）六种约束
    字段1，数据类型[列级约束条件][默认值],
    其他的类似字段1格式,
);
同时也要注意：1.创表时表名不能为sql语言的关键字，另外mysql是大小写不分的。2.创建字段间用逗号隔开（英文逗号）
8.show tables------》查看表是否创建成功。
9.primary key=unique+not null;(但是主键本身不带有自增长功能，因此一般主键加auto_increatment)
10.primary key(字段1，字段2)；------》联合主键的写法，当然这是写在创表过程中的。
11.create table 从表(
    id int(11) primary key,
    name varchar(25),
    deptid int(11),
    CONSTRAINT fk_emp_dept1 foreign key(deptid) references 主表(id);
)
创建外键关联关系
12.desc 表------》查看表结构
13.show create table 表名;------》但是我建议不要在mysql客户端中打开，在cmd打开比较好，因为客户端打开倒空间不足以让你看到所有table信息
14.alter table 表名 rename to 新表名 ------》修改表名（to 可选）
15.alter table 表名 modify 字段名 数据类型 ------》修改字段数据类型
16.alter table 表名 change 旧字段名 新字段名 新数据类型------》修改字段名
17.alter table 表名 add 新字段名 数据类型 [约束条件][First|after 已存在的字段名]------》添加字段,后面的约束条件啊什么的，是可选范围，其中first是指将该字段变为第一字段，after 已存在的字段名 指的是将该字段安置在已存在的字段名后面
18.alter table 表名 drop 字段名------》删除字段
19.alter table 表名 modify 字段1 数据类型 first|after 字段2------》修改字段排列位置
20.alter table 表名 engine=引擎名 ------》更改标的引擎      
21.alter table 表名 drop foreign key 外键约束名------》删除表的外键约束
22.drop table 表1，表2，表3........表n------》删除表；(如果你要删除一个和其他表有关联关系的表，那么必须先解除关联关系)(删除表必须谨慎，因为删除前没提示，而且还不能恢复)
23.SELECT VERSION(); ------》查询MYSQL的版本号
24.SELECT CONNECTION_ID(); ------》查看当前用户的连接数
25.show processlist;------》展示当前前100条连接用户信息，查看当前的连接状态，帮助识别出有问题的查询语句等。
26.show full processlist;------》展示全部用户连接信息
27.select 字段1,字段2 from 表|视图 where 查询条件------》这是MYSQL的最基本的查询语言
28.select 字段1,字段2 from 表 where 字段3 in(条件1,条件2) order by 字段1;------》Mysql带in的查询语言，即带范围的查询，当字段3的值与条件1或者条件2的内容相等则输出该内容
29.select 字段1,字段2 from 表 where 字段1 between 2.0 and 10.20 ------》MYsql的区间查询，该区间查询是在2.0 到 10.2之间，只要该区间符合，就返回匹配项目
30.select 字段1,字段2 from 表 where 字段2 like 'b%';------》like 模糊查询,该查询方式是指查询b开头的项目
31.select 字段1 from 表 where 字段2 is null; ------》非空判断，可以判断字段2是否为空
32.select 字段1 from 表 where 字段2=‘’ and 字段3>=5; ------》带and的多条件查询，这是一个且的意思，增加了查询限制
33. select 字段1 from 表 where 字段2=‘’ or 字段3>=5;------》 带or的多条件查询，这是一个或的意思，符合其中一个条件即可以返回匹配项目(注意ad 和 or可以联合使用，但是and先计算再计算or的)
34.select distinct字段1 from 表 ;                     ------》  DISTINCT(中文意思是有区别的)是一个消除重复值的关键字，该查询意思是从表中查询字段1不重复对象
35.SELECT 字段1,GROUP_CONCAT(字段2) AS 新集合字段  FROM 表 GROUP BY 字段2 HAVING COUNT(字段2)>1;      ------》   group by 分组查询，通常和MAX(),MIN(),COUNT(),SUM(),AVG()一起使用 其中GROUP_CONCAT(字段1) AS NAMES意思是将字段1重新锻造成一个可以包含集合的字段
36.select 字段1 ,count(*) as 新字段 from 表 group by 字段2 with rollup                       --------》with rollup(中文意思汇总)代码意思是汇总字段2的相同字段，并且给出相同属性数目，在最后多添加一个统计列， 统计记录数量
37.SELECT * FROM 表 LIMIT 2 , 3;             --------》代码意思：调取第二条记录的后三条记录  ， LIMIT是从0开始计算起来的，如果LIMIT 0,2;等价于 LIMIT 2;前2条记录的调取  LIMIT 2 , 3;等价于 LIMIT 2 OFFSET 3
38.SELECT COUNT(*) AS 新字段 FROM 表 ;        --------》代码意思总计表中数据记录数，count()记录表中行数 ，这就是集合函数查询，另外还有sum,avg,max,min等函数，他们使用在select的后面
39.SELECT 字段1,字段2 FROM 表 WHERE 字段=值;  --------》多字段单表查询，字段和字段之间用逗号隔开
40.select (表1的)字段1,(表2的)字段1 from 表1 s(表1的别名，方便书写) inner join  表2  f(表2的别名) where s.字段2=f.字段2;(当条件值相等，就可以得出匹配项目)   --------》内连接方式，两表连接方法
41.SELECT 别名1.字段1 ,别名2.字段2 FROM 表 别名1 INNER JOIN 表 别名2 WHERE 别名1.字段=别名2.字段 AND 别名1.字段='2';--------》内连接，两表连接当中特殊的内连接，互相连接的表在物理上为同一张表。
42.SELECT e_name,e_salary FROM employee WHERE e_salary IN(SELECT MIN(e_salary)  FROM employee WHERE e_job='销售部')AND e_job='销售部' ;
   SELECT e_name,e_salary FROM employee WHERE e_salary = (SELECT MIN(e_salary)  FROM employee WHERE e_job='销售部')AND e_job='销售部' ;
   这是带IN的子查询，in等价于'=',同时也要注意的是 在使用带in 子查询时，where 后面的字段要与子查询的查询字段要一致，否则报错。
43.查询某个表中的某个数据大于2的信息
SELECT NAME,COUNT(*) AS total FROM  sys_role s JOIN sys_user_role u ON s.id=u.role_id  GROUP BY  u.user_id HAVING total>2;
44.增加语句:INSERT INTO 表(字段)     VALUE(值)
45.<=> 安全等于运算符，该符号和"=" 操作符执行相同的比较操作，唯一的区别是 1代表两者不为null ,0代表其中一方为null
46.least和GREATEST是相反的两个运算符，唯一相同是在null上面都做出null的显示， least是返回最小值，GREATEST是返回最大值， 格式是：SELECT GREATEST (2,0)  least同理
47.MYSQL中运算是在select 后面直接运算即可，如：SELECT  (9-7)*4,8+15/3,17 DIV 2,39 % 12
48.保留小数函数---》truncate(x,y)和round(x,y)指的是截取数字x是数字，y是需要截取的位置.但是truncate在截取时不会四舍五入，但是round会四舍五入。 如： round（2.55,1）结果显示2.6  truncate(2.555,2) 结果显示2.55 SELECT ROUND(3.14159,2)  -- 保留后面两个小数
49.函数---》pow和power是同样意思的，指的是平方算法  如 select pow(2,2)指的是2的2次方
50.函数---》exp函数是以e为底的几次方  如 select ex(2) 指的是e 的2次方
51.函数---》right 和 left的区别   select right(samlin,4) 结果显示 后方四个单词mlin ;  显示前方四个单词 select left(samlin ,4)  结果显示---》 saml
52.函数---》LPAD是填充函数，如LEAD('SAM',2,'LIN')这时不会填充，因为sam的字符串比2还大，所以不会填充，只会显示sa；当中间数字比第一个字符串数还大时，那么后面的字符串就会添加到它的前面来。
53.函数---》reverse反转字符串 select  reverse('sam')  结果显示：mas
54.函数---》field(s,s)返回首个字符串出现的位置，如：field(s,s) 结果显示   1  如果 field(s,s1,s) 结果显示   2
55.函数---》FIND_IN_SET 其参数只有两个，如  FIND_IN_SET(x,x) 具体例子：select FIND_IN_SET('hi','hey,hi'); 结果显示 2
56.函数---》 SELECT DAYNAME('2013-02-13')  根据日期查星期几
57.函数---》SELECT WEEK('2017-04-08')   查询一年过去了几周
58.函数---》判断函数是否正确：csae 和SELECT IF(1<2,'true','false')的道理是一致的：意思是表达式是否正确，正确则前者显示，不正确则后者显示
          例子：  SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END   
59.系统函数---》
                SELECT VERSION()      -- 显示mysql的当前版本号
                SELECT CONNECTION_ID()  -- 显示mysql服务器当前的连接次数
                SHOW PROCESSLIST    -- 显示当前用户连接信息
                SELECT DATABASE()  -- 查看当前使用的数据库
                SELECT SCHEMA()   -- 查看当前使用的数据库
                SELECT USER(),CURRENT_USER() ,SYSTEM_USER()  -- 获取当前登录用户名称

                SELECT CHARSET('abc'),CHARSET(CONVERT('abc' USING latin1)),CHARSET (VERSION())  -- 返回字符串使用的字符集
                SELECT COLLATION('abc') ,COLLATION(CONVERT ('abc' USING utf8))     -- 返回字符串排列方式
                SELECT LAST_INSERT_ID()    --  显示最后进行插入操作的数据的id值

60.INSERT INTO worker VALUE(NULL,'sam'),(NULL,'micale'),(NULL,'nick')  -- 向表中插入多条数据

61.mysql加密和解密函数
SELECT PASSWORD('newpwd')  
SELECT PASSWORD(NULL)
SELECT MD5('mypwd');
SELECT ENCODE('secret','cry') ,LENGTH(ENCODE('secret','cry'))
SELECT DECODE (ENCODE ('secret','cry'),'cry')
                            
62.格式化函数
format
如：SELECT FORMAT(123456.92345,0)  显示：123457  会四舍五入的

63.进制转换函数
如：SELECT CONV('a',16,2)  -- 将十六进制的a转换为二进制
结果显示：1010


64.ip地址与数字互相转换的函数
SELECT INET_NTOA(3520061480)  -- 将数值网络地址转换为字符串网络数值

SELECT INET_ATON('209.207.224.40') -- 将字符串网络数值转换为数值网络地址

65.带有using的convert(转换之意)是转变字符集的函数    

SELECT CHARSET( CONVERT ('string' USING latin1))  -- 将string字符串由utf-8转变到 latin1(拉丁语)

66.-- 生成10以内的随机数
SELECT ROUND(RAND()*10)

67.计算字符串的长度
如：
SELECT LENGTH('university')

68.函数----》重复输出同一个字符串
SELECT REPEAT ('Cheer',3)

69.截取函数
SELECT SUBSTRING('nice to meet you',9,4)AS one1   从nice to meet you中获取meet
意思是：从9开始获取4个字符

70.往前补充函数 LPAD ，中间数字指的是该字符串的长度，如果字符串短于该数字则往前填充后面字符串，如果大于则显示该段字符串即可
SELECT LPAD('hello' ,8,'a')
71.查询今天的日期
SELECT DAYNAME(CURDATE())
72.查询工作日    0代表星期一。。。。6代表星期日
SELECT  WEEKDAY(CURDATE())

73.SHOW PROCESSLIST -- 显示当前连接状态

74.encode进行加密 decode进行解密
SELECT ENCODE('mysql','cry')

SELECT DECODE(ENCODE('mysql','cry'),'cry')

75.将100从10进制变为16进制
SELECT CONV('100',10,16)
                    
76.将字符串'new string'的字符集改为gb2312            

select charset(convert('new string' using gb2312))


77.mysql的写法习惯：将mysql的关键字大写

78.不在某个范围内的查询  in查询(in查询都是有个括号，包住范围)
SELECT s_id,f_name,f_prince FROM fruits WHERE   s_id NOT IN(101 , 102) ORDER BY s_id

79.查询包含g字母的水果

SELECT f_id,f_name,f_prince FROM fruits WHERE f_name LIKE '%b%'

80.查询以g开头y结尾的水果

SELECT f_name FROM fruits WHERE f_name LIKE 'b%y'

81._ 是代表替代一个字符串

SELECT f_name FROM fruits WHERE f_name LIKE '____y';

82.查询某个字段不为空
SELECT * FROM customers WHERE c_email IS NOT NULL (查询字段为空的话将not 去掉就可以了)

83.or查询和in查询可以达到相同目的
如：
    SELECT s_id,f_name, f_prince FROM fruits WHERE s_id IN (101,102)
    
    SELECT s_id,f_name,f_prince FROM fruits WHERE s_id=101 OR s_id =102
    
84.or和and是可以同时使用的，但是在一起使用时要注意两者的优先级，由于and的优先级别比or高，所以先对于and 操作，再到or

85.排除重复查询
SELECT DISTINCT s_id FROM fruits     

86.使用group by 分组对 重读元素进行统计
SELECT s_id,COUNT(*) AS total  FROM fruits GROUP BY s_id    

87.group_concat函数 可以在group by 中使用 group_concat()函数   将分组中的各个字段显示出来
如
SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id

88.显示s_id对fruits表中的数据进行分组，并且显示水果种类大于1的分组信息
 SELECT s_id,GROUP_CONCAT(f_name) AS NAMES FROM fruits GROUP BY s_id HAVING COUNT(f_name)>2

89.两表查询(内连接)，使用比较运算符进行表间某些数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录（满足条件的记录才能出现在结果关系当中）
如     SELECT sam.id,sam.s_name,sam1.s_name FROM sam ,sam1 WHERE sam.s_name=sam1.s_name

90.计算每个部门有多少人，
解题思路，先分组group by 再 使用count(*)
SELECT  dept_no,COUNT(*) FROM employee GROUP BY dept_no
91.计算工作年限
SELECT * FROM employee WHERE YEAR(CURDATE())-YEAR(hiredate)>15
或者
SELECT * FROM employee WHERE YEAR(CURRENT_DATE-hiredate)>20

92.正则表达式查询

搜索出以ns其中一个为结尾的单词和以s开头的单词

    SELECT * FROM employee WHERE e_name  REGEXP '[ns]$'
    SELECT * FROM employee WHERE e_name REGEXP '^[s]'
    
93 ‘.’代表一个字符
    SELECT * FROM fruits WHERE f_name REGEXP 'a.g'
    显示oranger
    
94.'*'和'+'代表多个字符

95.计算相差年份
SET @date1='1929-02-14'
SELECT @date1
SET @date2=YEAR(CURRENT_DATE)
SELECT @date2
SELECT @date2 - @date1

96.最基本的索引类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。
创建普通索引：
CREATE TABLE book(
    booid INT(10),
    year_publication YEAR NOT NULL,
    INDEX(year_publication)
)

创建唯一索引：
CREATE TABLE book1(
    booid INT(10),
    year_publication YEAR NOT NULL,
     UNIQUE INDEX(year_publication)
)

创建符合索引
CREATE TABLE book3(
    booid INT(10),
    year_publication YEAR NOT NULL,
    INDEX 复合索引名字 (year_publication,booid)
)


97.如何在创表时改变引擎
CREATE TABLE book4(
    booid INT(10),
    year_publication YEAR NOT NULL,
    sam VARCHAR(10),
    FULLTEXT INDEX fulltext1(sam)
)ENGINE=MYISAM  
直接写上ENGINE=。。。。。即可以改变引擎


98.为已存在的添加索引
ALTER TABLE book ADD INDEX bknameidx(booid)

99.删除索引
ALTER TABLE book1 DROP year_publication

100.delete语句
delete from employee;删除整个表里面的信息，但是会保留表结构
delete from employee where lastname = 'May';删除某个具体的部分