sql基本语法 - 《SQL》

1. create table命令
2.举例：

access denied for user root@localhost
出了一个问题
在客户端当中进入mysql修改一下root的密码在重新登录
修改密码的语句 update user set password=password(“123456”)再用小黑框进就好了
启动过程
sqlcmd -s DESKTOP-Q3VQ7U1 -U sa -P 123456 -d db_test -r -i G:\test.sql貌似可以小黑框运行脚本
cmd启动小黑框需要管理员权限
net start mysql 启动mysql服务器
net stop mysql 关闭服务
mysql -uroot -p 进入数据库远程的话 mysql -h127.0.0.1 -uroot -p
show databases 显示所有数据库名称
use 数据库名称进入数据库
CREATE DATABASE mydb1 创建一个数据库命名mydb1
CREATE DATABASE IN NOT EXISTS 上面如果存在会报错这么写不会报错
DROP DATABASE mydb1 删除数据库
ALTER DATABASE mydb1 CHARACTER SET utf8 知道有就行修改编码的
int :整型
double 浮点型，double(5,2)表示最多五位，其中必须有两位小数，即最大值为999.99
decimal 浮点型，在表单钱方面使用该类型，因为不会出现精度缺失问题。
char：固定长度字符串类型：char(255)数据长度不足指定长度，补足到指定长度
varchar 可变长度字符串类型 varchar(65535) zhangsan
text(clob) 字符串类型
创建表：
CREATE TABLE 表名 (ITCAST_0001
列名列类型，
列名列类型，
。。。
列名列类型，
);后面可以加东西 engine=innodb意思是存储引擎是innodb nnodb是mysql上第一个提供外键约束的数据存储引擎，除了提供事务处理外，innodb还支持行锁

1. create table命令

强调：使用建表命令之前必须使用use命令选择表所在的数据库。create table命令的格式如下：

create table tablename(
     col_name1 type auto_increment,
     col_name2 type not null|null default val,
     col_name3 type unique,
     .... ,
    primary key(col_name),
    index idx_name(col_name1,col_name2,...),
    foreign key(col_name) references tablename(col_name)
) engine=innoDB|myISAM default charset=字符集;
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

说明：
（1）type：定义字段的数据类型。其中：字符串类型（char，varchar）需要指定长度；整型（int，biging，tinyint等）和日期类型（datetime，timestamp等）只需要指定类型，不需要指定长度；decimal类型需要指定精度和小数位数。
（2）primary key参数指定表的主键。
（3）auto_increment参数指定字段为自增字段，该字段的类型必须为int或bigint才能设置为自增字段。
（4）not null|null参数设置字段能否取空值。
（5）default参数设置字段的默认值。
（6）index参数用来创建索引。idx_name指定索引名，该参数可以省略，如果省略则索引名就是字段名。
（7）foreign key参数用来指定外键。
（8）engine参数用来设置引擎类型，常用的有innoDB和myISAM引擎。
（9）default charset参数设置表所用的字符集。

2.举例：

（1）创建dept表：

create table dept(
    dept_id int primary key auto_increment,    --dept_id设置为int类型，自增字段，主键
    dept_name char(20) not null default '',    --dept_name不能为空，默认值为空字符串
    phone char(20)
);
• 1
• 2
• 3
• 4
• 5

（2）创建emp表：

create table emp(
    emp_id int auto_increment,
    emp_name char(20) not null default '',
    birth datetime not null,
    phone char(20) not null,
    addr varchar(200) not null,
    dept_id int not null default 0,
    index idx_emp_name(emp_name),
    index idx_emp_name_birth(emp_name,birth),
    primary key(emp_id),               --emp_id字段为主键
    foreign key(dept_id) references dept(dept_id)
) engine=innoDB default charset=utf8;

desc 查看表结构
drop table 表名;删除表
alter table 表名功能 ();
功能:
add 添加
modify 修改列类型
alter TABLE dept modify local varchar(15);
change 修改列名新列名后面要加上类型
alter TABLE dept change loc local varchar(20);
drop 删除列
rename to 修改表名称
SQL Structured Query Language 结构化查询语言大致分类:
DDL Data Definition Language : 数据定义语言，用来定义数据库对象：库、表、列等；
创建、删除、修改：库、表结构
DML Data Manipulation Language : 数据操作语言，用来定义数据库记录
增删改表记录
DCl Data Control Language ：数据控制语言，用来定义访问权限和安全级别
DQL Data Query Language : 数据查询语言，用来查询记录(数据)
insert into 表名（类型，类型。。。）value（对应的信息）;
字符串类型要用单引号日期类型也是。
也可以插入部分列，没有插入的默认为null
第三种插入方式
insert into 表名 values（所有信息）;
update 表名 set 命令 where 额外条件修改信息
update stu set age=18 where name=‘lisi’or number=‘ITCAST0003’;
满足这两个之一都会被修改
UPDATE stu set age=age+1 WHERE age>=20 and age <= 30;
等同于 UPDATE stu set age=age+1 WHERE age BETWEEN 20 and 30;
UPDATE stu set age=20 WHERE name in (‘zhangsan’,’lisi’); in的用法
not 就是取反
只要出现等于null就是false 想用就不能写等于要写 name is null
delete from 表名 where 条件；删除
一个项目创建一个用户，一个项目对应的数据库只有一个
这个用户只能对这个数据库有权限，其他的数据库不能操作
创建用户
CREATE USER zhangsan@localhost identified by ‘123’;
授权
GRANT all on db1. to zhangsan@localhost;
用张三登录
mysql -uzhangsan -p123
撤销授权
REVOKE delete on db1. FROM zhangsan@localhost;
查看权限
SHOW GRANTS for zhangsan@localhost;
删除用户
DROP USER zhangsan@localhost;
查询指定列
SELECT ename,sal FROM emp; 查询ename和sal
SELECT distinct ename,sal FROM emp; distinct 去除重复行
SELECT ,sal1.5 FROM emp; 列运算不识别的就当成0
SELECT ,sal+ifnull(comm,0) FROM emp; 把null变成0
mysql中不能用+连接字符串
SELECT concat(‘我叫’,ename,’工作是’,job) FROM emp; 可以用这种方式
SELECT ename as ‘姓名’, job as ‘工作’ FROM emp; as 可以省略
SELECT FROM emp WHERE empno LIKE ‘101‘; 模糊查询 101什么都可以都能查不过只有一位五位不行
SELECT FROM emp WHERE ename LIKE ‘zhang%’; % 代表零个或多个
SELECT FROM emp ORDER BY sal DESC; 查询结果排序，默认升序 ASC可省略倒序 DESC
SELECT FROM emp ORDER BY sal ASC,comm DESC,empno ASC; 若第一个相同，就看后面条件
SELECT count() FROM emp; 查询有效行多少个
SELECT SUM(sal) FROM emp; 累加值
max min avg 一共五个聚合函数
SELECT count(),job FROM emp GROUP BY job; 分组查询
SELECT count(),job,max(sal),ename FROM emp GROUP BY job; 一般都是分组列和聚合函数，有意义
SELECT deptno,count() FROM emp WHERE sal>15000 GROUP BY deptno having count()>=2;
where是分组前条件 having是分组后条件
方言limit
SELECT FROM emp LIMIT 0,5; 从零行开始查查5行
找出部门编号为10中的所有经理，和部门编号为20中的所有销售员的详细资料
SELECT FROM emp WHERE deptno=10 and job=’jingli’ or deptno=20 and job=’xiaoshouyuan’;
找出部门编号为10中的所有经理，和部门编号为20中的所有销售员的详细资料还有既不是经理也不是销售员但是工资大于或等于两万的所有员工的详细资料
SELECT FROM emp WHERE deptno=10 and job=’jingli’ or deptno=20 and job=’xiaoshouyuan’or job not in (‘jingli’,’xiaoshouyuan’) and sal >=20000;
查看mysql数据库编码
SHOW variables LIKE ‘char%’;
character set client = utf8 无论客户端发送的是什么编码的数据都当成utf8
character set results = utf8 mysql发送给客户端的数据都是utf8
处理手段有两种
让客户端发送utf8数据(行不通)
把character set client 修改为gbk 只在当前窗口有效
character_set_results=utf8,把数据用什么编码发送给客户端返回来的编码
若服务器发送给客户端的是utf8
客户端会把它当成gbk 因为小黑瓶只显示gbk
所以乱码
让服务器发送gbk的数据
让小黑屏使用utf8来解读(行不通)
my.ini
在总配置文件中进行配置，可以一劳永逸
[client]
port=3306
[mysql]
default-character-set=gbk 能修改三个 client result connection
net stop mysql 停止服务
net start mysql 启动服务
备份与恢复
数据库 —> sql语句
sql语句 —> 数据库
数据库导出sql脚本（备份数据库内容而不是备份数据库）
mysqldump -uroot -p123 db1>d:/a.sql
导出的文件名字上都有小撇，那个不是单引号而是~键位
mysql -uroot -p123 db1第二种方式
登录mysql
use 数据库进来之后用source
source d:/a.sql恢复
恢复要先建库然后再恢复
创建时定义主键
CREATE TABLE EMP2(
empno INT PRIMARY KEY,
ename VARCHAR (50)
);
主键非空不能重复可引用
第二种形式
CREATE TABLE EMP2(
empno INT ,
ename VARCHAR (50),
PRIMARY KEY(empno)
);
ALTER TABLE emp ADD PRIMARY KEY(empno); 这种方式也是添加主键
ALTER TABLE emp DROP PRIMARY KEY; 利用修改表的方式撤销主键主键唯一所以不用特指
CREATE TABLE emp3(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
); 主键自增长多个电脑构成的服务器不好用自增长和主键是一起用的
非空约束
建表后面加个 not null
唯一约束
建表后面加个 not unique
外键约束
外键必须是表的主键
可以重复
可以为空
一张表中可以有多个外键
CREATE TABLE emp (
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(50),
dno INT,
CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno)
);外键约束的语法
Cannot add or update a child row: a foreign key constraint fails (db2.emp, CONSTRAINT fk_emp_dept FOREIGN KEY (dno) REFERENCES dept (deptno))违反外键约束报错
也可以创建之后在定义
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept FOREIGN KEY(dno) REFERENCES dept(deptno);
一对一代码
CREATE TABLE wife(
wid INT PRIMARY KEY AUTO_INCREMENT,
wname VARCHAR(50),
hid INT,
CONSTRAINT fk_wifr_hashband FOREIGN KEY(wid) REFERENCES hashband(hid)
);此时wid有三种限制非空唯一引用hid
从表的主键即是外键
多对多的代码
CREATE TABLE stu_tea(
sid INT,
tid INT,
CONSTRAINT fk_student FOREIGN KEY (sid) REFERENCES student(sid),
CONSTRAINT fk_teacher FOREIGN KEY (tid) REFERENCES teacher(tid)
); 两个表本身没关系新加的这张表上面能看出它们建立的关系
多表查询
分类合并结果集连接查询子查询
合并结果集要求两个表结构要一样实际就是要求两个结果集列数相同并且每列类型相同
SELECT FROM ab
UNION ALL 要是没有all 完全相同的会被去除
SELECT FROM cd;
连接查询分内外连接
内连接
方言形式：
SELECT FROM emp,dept; 结果是 15x4等于60条笛卡尔积
用条件去除无用的笛卡尔积
where emp.deptno=dept.deptno;
SELECT e.ename,e.sal,d.dname
FROM emp e,dept d
WHERE e.deptno=d.deptno; 给表起别名能简单一些
标准格式：
select from 表1 别名1 inner join 表2 别名2 on 别名1.xx=别名2.xx
自然连接：
select from 表1 别名1 natural join 表2 别名2
外连接：
左外连接就是左表为主主表中所有的记录无论是否满足条件都打印，不满足条件时右表null
SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno=d.deptno;
右外一样
SELECT e.ename,e.sal,IFNULL(d.dname,’无部门’) ‘部门’
FROM dept d RIGHT OUTER JOIN emp e
ON e.deptno=d.deptno;
全外连接
mysql不支持可以用union关联左外和右外结果来模拟
子查询
工资最高的人的全部信息
单行单列
SELECT FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);
from 后面做表存在 where后面做条件存在
多行单列
SELECT FROM emp WHERE sal> ALL (SELECT sal FROM emp WHERE deptno=30); 大于所有
搜索大于任意一个经理的工资的员工的信息
SELECT FROM emp WHERE sal> ANY (SELECT sal FROM emp WHERE job=’jingli’); 大于任意
单行多列
SELECT FROM emp WHERE (job,sal,deptno) IN (SELECT e.job,e.sal,e.deptno FROM emp e
WHERE ename=’yintianzheng’);
多行多列
from 后面一般都是多行多列
查出至少有一个员工的部门，显示部门编号、部门名称、部门地址、以及部门人数
SELECT d.deptno,d.dname,d.loc,e.con FROM dept d INNER JOIN (SELECT deptno,COUNT() con FROM emp GROUP BY deptno) e ON d.deptno=e.deptno;
查询每个员工对应的领导的名字
SELECT e.ename,m.ename ‘领导’ FROM emp e INNER JOIN (SELECT empno,ename FROM emp
) m ON m.empno=e.mgr;
列出受雇日期遭遇直接上级的所有员工的编号、姓名、部门名称
SELECT e.empno,e.ename,d.dname FROM (SELECT e.empno,e.ename,e.deptno FROM emp e INNER JOIN (SELECT FROM emp) m ON m.empno=e.mgr AND e.hiredate列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
SELECT d.dname,e.
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno=e.deptno;
列出最低薪金大于一万五的各种工作以及从事此工作的员工人数
SELECT nb.job,COUNT() FROM (SELECT e. FROM emp e INNER JOIN (SELECT job FROM (SELECT job,MIN(sal) ax FROM emp GROUP BY job) j WHERE j.ax>15000
) jj ON e.job = jj.job) nb GROUP BY nb.job;
列出销售部的员工的姓名，假定不知道销售部编号
SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname = ‘xiaoshoubu’);
查看工资大于公司平均工资的员工信息、部门名称以及领导和自己的工资等级
SELECT e.,d.dname,m.ename ‘领导’,s.grade
FROM emp e LEFT OUTER JOIN dept d ON e.deptno= d.deptno
LEFT OUTER JOIN emp m ON e.mgr= m.empno
INNER JOIN salgrade s ON e.sal BETWEEN losal AND hisal
WHERE e.sal>(SELECT AVG(sal) FROM emp);
查出年份、利润、年度增长比
SELECT y1.year,y1.zz,y2.zz, IFNULL(CONCAT((y1.zz-y2.zz)/y2.zz100,’%’),’0%’) ‘增长比’
FROM tb_year y1 LEFT OUTER JOIN tb_year y2
ON y1.year=y2.year+1
jdbc
导jar包驱动
加载驱动类 Class.forName(“类名”);
给出 url、username、password，其中url背下来
jdbc四大配置参数：
driverClassName ：com.mysql.jdbc.Driver
url：jdbc:mysql://localhost:3306/db1
username：root
password：123
Class.forName(“com.mysql.jdbc.Driver”);
Connection con =DriverManager.getConnection(
“jdbc:mysql://localhost:3306/db1”, “root”,”123”);
System.out.println(con);
String sql = “INSERT INTO ab VALUES (5,’5’)”;
int r = stmt.executeUpdate(sql);
这两句可以ddl、dml语言
ResultSet rs = stmt.executeQuery(“select from emp”);
while(rs.next()) {
int empno = rs.getInt(1);
String ename = rs.getString(“ename”);
double sal = rs.getDouble(“sal”);
System.out.println(empno+”,”+ename+”,”+sal);
} 查询方式