基础的SQL语法详解

#查看表结果

desc t_stu

#查看表结构

show create table t_stu

#修改表名

alter table t_stu_detail rename t_detail

#删除表结果

drop table t_detail

#修改表结构

alter table 表名 add 列/约束/索引

alter table 表名 drop 列/约束/索引

alter table 表名 modify/change列定义(包括列约束)

#增加一列

alter table t_stu add age int(11) [after/first] sex

#删除列

alter table t_stu drop age

#修改列类型

alter table 表名 modify 【column】 列名 数据类型;

alter table 表名 modify 【column】 列名 数据类型 after 某一列;

alter table 表名 modify 【column】 列名 数据类型 first;

#修改列名等

alter table 表名 change 【column】 列名 新列名 数据类型;

#增加主键约束

alter table 表名称 add 【constraint 约束名】 primary key (字段名);

alter table 表名称 add 【constraint 约束名】 primary key (字段名1，字段名2);#复合主键

#增加唯一键约束

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名);

alter table表名称 add 【constraint 约束名】 unique 【key】 (字段名1，字段名2);

#增加外键约束

alter table表名称 add 【constraint 约束名】 foreign key (从表字段名) references 主表名(主表被参照字段名);

#增加/删除缺省约束或非空约束

alter table 表名 modify 【column】 列名 数据类型 【default 默认值】 【not null】;

#增加索引

CREATE INDEX index_name ON table_name (column_name,[column_name...]);  最左边的列最关键

DML:数据操纵语言（DML）DML用于插入、修改、删除数据记录

DRL:DRL的查询语言是select语句，可以包含5种子句：

依次是where、 group by、having、 order by、limit

MySQL的运算符

（1）算术运算符：+  -  *  /（除也可以写成p)   %（取模可以写成mod）

（2）比较运算符：=  >  >=  <  <=  !=（不等于还可以写成<>）

（3）逻辑运算符：&&（逻辑与也可以写成and） ||（逻辑或也可以写成or） not（逻辑非）

（4）范围：表达式 between ... and ... （也可以写成 表达式>=...  and 表达式 <=...）

            表达式 not between ... and ...（也可以写成 表达式<...  || 表达式 >...）

（5）集合：in (值，值，值...)  not in(值，值，值...)

（6）模糊查询：LIKE  NOT  LIKE，通配符：%表示0-n个字符，_下划线代表一个字符

（7）位运算符：&（按位与） |（按位或）  注意：极少用，可忽略

（8）NULL值判断，is null 或 is not null，如果使用null=null，null<>null,null=0,null<>0,null=false等都不对

DCL:DCL用来控制数据库的访问

IN：等于任何一个

　　ALL：和子查询返回的所有值比较。例如：sal>ALL(1,2,3)等价于sal>1 && sal>2 && sal>3，即大于所有。

　　ANY：和子查询返回的任意一个值比较。例如：sal>ANY(1,2,3)等价于sal>1 or sal>2 or sal>3，即大于任意一个就可以。

　　EXISTS：判断子查询是否有返回结果（不关心具体行数和内容），如果返回则为TRUE，否则为FALSE。

复制表（自学）

（1）拷贝表结构

CREATE TABLE newadmin LIKE admin;

（2）拷贝表结构和数据（但约束与索引除外）

CREATE TABLE newadmin AS   (   SELECT *  FROM admin  )  ;

（3）拷贝表结构+数据

CREATE TABLE newadmin LIKE admin;   

INSERT INTO newadmin SELECT * FROM admin;

（4）跨数据库拷贝表

CREATE TABLE newadmin LIKE shop.admin;   

CREATE TABLE newshop.newadmin LIKE shop.admin;

（5）拷贝一个表中其中的一些字段（指定新名），其中一些数据

CREATE TABLE newadmin AS   

(   

SELECT id, username AS uname, password AS pass FROM admin  WHERE id<10

)  ;

（6）在创建表的同时定义表中的字段信息。

create table tt

(

    eid int primary key auto_increment

)

as

(

    select employee_id as eid,first_name,last_name,email from employees

);

复制数据（自学）

在 INSERT 语句中加入子查询。

不必书写 VALUES 子句。

子查询中的值列表应与 INSERT 子句中的列名对应。

INSERT INTO emp2

SELECT * FROM employees WHERE department_id = 90;

或

INSERT INTO sales_reps(id, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

FROM   employees

WHERE  job_id LIKE '%REP%';

数学函数

MOD(x,y):返回x除以y以后的余数

SQRT(x)返回x的平方根

POW(x,y)、POWER(x,y)返回x的y次方

TRUNCATE(x,y)返回x保留到小数点后y位的值，之后舍去

ROUND(x,y)保留x小数点后y位的值，单截取时要进行四舍五入

CEIL(x)、CEILING(x)返回大于或等于x的最小整数

FLOOR(x)返回小于或等于x的最大整数

字符串函数

CHAR_LENGTH(s)返回字符串s的字符数

LENGTH(s)返回字符串s的长度，字节数

CONCAT(s1,s2...)将字符串合并为一个字符串

CONCAT_WS(x,s1,s2)将字符串合并为一个字符串，每个字符串之间要加x分隔

INSERT(s1,x,len,s2)用字符串s2替换s1的x位置开始长度为len的字符串

UPPER(s),UCAASE(s)将字符串s的所有字母变成大写字母

LOWER(s)、LCASE(s)将字符串s的所有字母变成小写字母

LEFT(s,n)返回字符串s的前n个字符

RIGHT(s,n)回字符串s的后n个字符

LPAD(s1,len,s2)字符串s2来填充s1的开始处，使字符串长度达到len

RPAD(s1,len,s2)字符串s2来填充s1的结尾处，使字符串长度达到len

LTRIM(s)去掉字符串s开始处的空格

RTRIM(s)去掉字符串s结尾处的空格

TRIM(s)去掉字符串s开始和结尾处的空格

TRIM(s1 FROM s)去掉字符串s中开始处和结尾处的字符串s1

REPLACE(s,s1,s2)将字符串s2替代字符串s中的字符串s1

SUBSTRING(s,n,len)获取从字符串s中第n个位置开始长度为len的字符串

日期和时间函数

CURDATE(),CURRENT_DATE()返回当前日期

CURTIME(),CURRENT_TIME返回当前时间

NOW(),CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),LOCALTIMESTAMP()返回当前日期和时间

MONTH(d)返回日期d中的月份值，1->12

MONTHNAME(d) 返回日期当中的月份名称，如Janyary

DAYOFWEEK(d)日期d今天是星期几，1星期日，2星期一

DAYNAME(d)返回日期d是星期几，如Monday,Tuesday

条件判断函数

IF(expr,v1,v2)函数,如果表达式expr成立，返回结果v1；否则，返回结果v2。

IFNULL(v1,v2)函数,如果v1的值不为NULL，则返回v1，否则返回v2

CASE

　　WHEN e1　THEN v1

　　WHEN e2　THEN v2

　　...

　　ELSE vn

END

CASE表示函数开始，END表示函数结束。如果e1成立，则返回v1,如果e2成立，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

或

CASE expr

　　WHEN e1 THEN v1

　　WHEN e2 THEN v2

　　...

　　ELSE vn

END

CASE表示函数开始，END表示函数结束。如果expr表达式的值为e1成立，则返回v1,如果为e2，则返回v2，当全部不成立则返回vn，而当有一个成立之后，后面的就不执行了。

经典问题：

1、在命令行出现乱码问题？

创建数据库时选择utf-8，但是windows窗口下是GBK，因此需要在命令行设置字符集 set names gbk；

是为了告诉服务器，客户端用的GBK编码，防止乱码

可以查看字符集

show variables like 'character_set_%'；

2、退出当前错误语句？

语句打错以后退出本语句，再继续打新语句

也可以打\c，快捷退出本语句

3、如何破解数据库密码？安全模式登录

   1、通过任务管理器或服务管理，关掉musqlId(服务进程)

   2、通过命令行+特殊参数开启mysqlId

           mysqlId --skip-grant-tables

   3、此时，mysqlId服务进程已经打开，并且，不需要权限检查

   4、mysql -uroot 无密码登录服务器

   5、修改权限表

      A: use mysql;

            B: update user set Password = password('123456') where User = 'root';

      C: flush privileges;

    6:通过任务管理器,关掉mysqld服务进程.

  7:再次通过服务管理,打mysql服务。

  8:即可用修改后的新密码登陆.

1、既复制表结构也复制表内容的SQL语句：CREATE TABLE tab_new AS SELECT * FROM tab_old;

2、只复制表结构不复制表内容的SQL语句：CREATE TABLE tab_new AS SELECT * FROM tab_old WHERE 1=2;

3、不复制表结构，只复制内容的sql语句：insert into tab_new select * from tab_old;或者SELECT vale1, value2 into Table2 from Table1