mysql笔记4

来源：互联网发布：vim python 开发环境编辑：程序博客网时间：2024/06/07 05:42

2017/11/3
SQL函数（聚合函数，标量函数(大小写，长度)，mid函数(显示形式)，as别名，union，add，drop，修改表中某列的数据类型，主键限制，外键限制，非空限制，truncate/delete /drop ，having 语句中嵌套子查询，补充知识，执行事务，pysql

subl 折行符\
=====================================================================
1.delete语句嵌套子查询
delete from 表名1 where 列名操作符](select 列名 from 表名2 [where 条件]);
注意:delete与update语句嵌套子查询的时候，子查询中涉及的表一定不同于主句中的表。
=====================================================================2.update语句嵌套子查询
update 表名1 set 列名操作符新的值 where 列名操作符 (select 列名 from 表名2 where 条件);

实例：
delete from customers_1 where age in (27,32);
delete from customers_1 where age in (select age from customers where age >= 27);
delete from customers_2 where salary > (select avg(salary) from customers_1);
update customers_2 set saary = salary *2 where age > (select avg(age)) from customers_1);
=====================================================================
3.sql函数
（1）sql aggregate 聚合函数标量函数mid函数
avg():平均值
count()：行数
max()：最大值
min()：最小值
sum()：求和
（2）sql scalar标量函数
ucase():upper case以大写字母显示
lcase():lower case以小写字母显示
length():求字符长度,包括小数点
round(column,n):对某个数字字段进行指定小位数的四舍五入,n表示位数
now():返回当前系统日期和时间
format():格式化某个字段的显示方式
练习：
（1）求出customers表中，地址为北京的顾客的总收入
select sum(salary) from customers where address = 'beijing';

（2）选出customers表中，工资大于所有顾客平均工资的人，并且以大写字母显示姓名，以小写字母显示住址
select ucase(name),lcase(address),salary from customers where salary > (select avg(salary) from customers);

（3）列出customers表中的工资（只取整数部分），工资的位数，以及当前系统的时间
select round(salary,0),length(salary),now() from customers;

（4）列出顾客表工资的位数
select length(salary)-3,name from customers;

（5）列出顾客表中，每个顾客工资与所有顾客的平均工资的差
正解：
select name,salary - (select avg(salary) from customers) from customers;
改进：
select name,salary - (select avg(salary) from customers) as difference from customers;
错误：
select name,avg(salary) - (select salary from customers) from customers;

（3）.mid函数（列名，起始位置，长度）：定义线束数据的长度
注意：第一个字母位置为1，不是0！！！！！
substring(列名，起始位置，长度) select mid(name,1,3) from customers;

实例：
select mid(name,1,3) from customers;
mysql> select mid(name,1,3) from customers;
+---------------+
| mid(name,1,3) |
+---------------+
| li |
| jer |
| Bob |
| aaa |
| bbb |
| ccc |
| xio |
| xia |
| Ann |
| xia |
+---------------+
10 rows in set (0.00 sec)
1：从起始位置为1开始
3：打印出3个字母的长度
=====================================================================
4.as别名：
select format(列名，新的格式) from 表名；
select date_format(now(),'%Y-%M-%D')as date from customers;

实例
mysql> select date_format(now(),'%Y-%M-%D')as date from customers;
+-------------------+
| date |
+-------------------+
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
| 2017-November-3rd |
+-------------------+

实例：
mysql> select now()as date from customers;
+---------------------+
| date |
+---------------------+
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
| 2017-11-03 10:43:50 |
+---------------------+
10 rows in set (0.01 sec)

=====================================================================
5.Union：综合多个select语句，且返回不重复的行
union all：返回重复的行（以适应存在数据相同的情况）
注意：
（1）每个select语句中必须选中相同数量的列
（2）列的数目要一致
（3）列的数据类型要一致
（4）列的顺序要一致

练习：
选中customers表中，年龄小于25和大于27岁的人的姓名和年龄
法一：
select name,age from customers where age < 25 or age > 27;
法二：
select name,age from customers customers where age <25
union
select name,age from customers customers where age >27;
mysql> select name,age from customers customers where age <25
-> union
-> select name,age from customers customers where age >27;
+--------+------+
| name | age |
+--------+------+
| jeremy | 20 |
| xioayu | 24 |
| Ann | 22 |
| li | 30 |
+--------+------+
4 rows in set (0.00 sec)

思考：
选出customers表中，所有顾客的工资，并且最后一行打印出顾客的总工资
select salary from customers
union
select sum(salary) from customers;

mysql> select salary from customers
-> union
-> select sum(salary) from customers;
+-----------+
| salary |
+-----------+
| 2645.00 |
| 2300.00 |
| 12894.38 |
| 8596.25 |
| 60000.00 |
| 90000.00 |
| 30000.00 |
| 3000.00 |
| 235224.39 |
+-----------+
=====================================================================
6.add:向表中添加一个新的列
alter table 表名add 列名数据类型

实例：
alter table customers add gender char(20);
mysql> select * from customers;
+------+----------+------+-----------+----------+--------+
| ID | NAME | AGE | ADDRESS | SALARY | gender |
+------+----------+------+-----------+----------+--------+
| 2 | li | 30 | beijing | 2645.00 | NULL |
| 3 | jeremy | 20 | shanghai | 2300.00 | NULL |
| 4 | Bob | 25 | beijing | 12894.38 | NULL |
| 5 | aaa | 25 | beijing | 12894.38 | NULL |
| 6 | bbb | 25 | beijing | 12894.38 | NULL |
| 7 | ccc | 27 | beijing | 8596.25 | NULL |
| 8 | xioayu | 24 | shenzhen | 60000.00 | NULL |
| 9 | xiaohu | 25 | guangzhou | 90000.00 | NULL |
| 1 | Ann | 22 | zhuhai | 30000.00 | NULL |
| 10 | xiaoming | 25 | beijing | 3000.00 | NULL |
+------+----------+------+-----------+----------+--------+
=====================================================================
7.drop：向表中添加一个新的列
alter table 表名 drop column 列名；
实例：
alter table customers add gender char(20) default 'F';
alter table customers drop column gender;
=====================================================================
8.修改表中某列的数据类型
alter table 表名 modify column 列名新的数据类型
实例：
alter table customers modify column salary float;
=====================================================================
9.总结：
微观修改表中的元素宏观修改表整体结构
修改 update alter...modify column...
删除 delete alter...drop column...
添加 insert alter...add column...

=====================================================================
10.主键限制
对表取消主键限制:
alter table 表名 drop primary key;
实例：
show create table courses;
alter table courses drop primarykey;
show create table courses;

对表添加主键限制:
alter table 表名 add primary key （列名）;
=====================================================================
11.外键限制
对表取消外键限制:
alter table 表名 drop foreign key 列名；

对表添加外键限制：
alter table 表名 add foreign key(列名) references 表名(列名)；
=====================================================================
12.非空限制：
对表添加非空限制
alter table 表名 modify 列名数据类型 not null;
alter table courses modify name varchr(20) not null;

对表删除非空限制
alter table 表名 modify 列名数据类型 ;
alter table 表名 modify 列名数据类型 default null;
alter table courses modify name varchr(20) default null ;

=====================================================================
13.truncate/delete :删除表数据,保留表头
truncate table 表名:保留表头，保留表的格式，只删除表中存放的数据内容
delete from table 表名：保留表头，保留表的格式，只删除表中存放的数据内容，一般与条件语句where连用

实例：
truncate table customers_1; 清空表中数据，show tables还可以看得到
delete from table customers_1; 清空表中数据，show tables还可以看得到
=====================================================================
14.drop :删除表(删除表数据,删除表头)
drop table 表名:删除表头和表中的数据
drop table customers_1; 删除表customers_1s

=====================================================================
15.having 语句中嵌套子查询：
select address,sum(salary) from customers group by address having address in ('Beijing','Hangzhou');

思考题：
查询customers表中，城市平均工资大于3000的城市名和工资信息
错误(可以运行，但是为空集，不是指定的问题的答案时)：
select address,salary from customers where salary <(select avg(salary)group by address);
正解:
select address,avg(salary) from customers group by address having avg(salary)>3000;

总结：
聚合函数要使用条件，只能使用having语句，不能使用where语句
聚合函数的条件 - having语句；
select语句的条件- where语句。
=====================================================================
16.补充知识：
删除外键：
用show create table 表名看系统自动生成的外键名,用新生成的名字来删除外键
例：alter table courses drop foreign key courses_ibfk_2;

添加外键：
例：alter table courses add foreign key（instructor） references teachers（name）;

修改表名
alter table 表名 rename 新表明
例：alter table customers rename customers_update;

修改列名
alter table 表名 change 旧名字新名字数据类型
例：alter table customers change id c_id int;

=====================================================================
17.执行事务
事务机制：确保数据一致性
事物的4个属性：
(1)原子性：
一个事务是一个不可分割的工作单位，事务中包括的各个操作要么都做，要么都不做；
(2)一致性：
事务必须是数据库从一个一致性状态变到另一个一致性状态；
(3)隔离型：
一个事务的执行不能被其他并发的事务干扰(多用户同时操作一个数据库不会影响)；
(4)持久性：
一个事务一旦提交，它对数据库中数据的改变表就应该是永久性的；

注意：
(1)mySQL中默认sql语句的结果自动commit到数据库，
(2)开始需要写start transaction这样自动commit就会被禁用，直到我们用commit或rollback终止transaction

注：rollback 终止，回滚 commit 提交 start transaction 开始事务

实例-执行事务完整操作：
start transaction;
insert into teachers values ('xiao','m','instructor');
insert into teachers values ('JIM','m','instructor');
commit;
select * from teachers;

实例-执行事务回滚（撤销）操作：
start transaction;
insert into teachers values ('Jeremy','m','instructor');
insert into teachers values ('CONDI','m','instructor');
rollback;
select * from teachers;
补充：rolllback之前可以在teachers表中查看到插入的两条教师信息，但是在执行rollback语句后，两条信息从表中消失；
意义：再用多条sql语句进行数据库的建设和使用时，可以用
start transaction;
commit;
rollback;
来设置断点，能够及时修改错误，撤销操作重新执行。

=====================================================================
18.python与MySQL-pysql

import pymysql
db = pymysql.connect('localhost','root','123456','testDB')
#----------------以上建立python终端与数据库的链接----------------------
cursor = db.cursor() #使用cuosor()方法创建一个游标对象实例cursor
#--------------------------------------------------------------------
sql = 'select * from customers;'
cursor.execute(sql) #使用execute()方法执行SQL绑定的语句，返回行数
print(cursor.execute(sql)) #在python3终端打印customers表的记录行数
data = cursor.fetchone() #开始抓取数据 iter() next()
print(data) #打印第一行数据
data = cursor.fetchone() #开始抓取数据
print(data) #打印第二行数据
data = cursor.fetchone() #开始抓取数据
print(data) #打印第三行数据
#------------以上:在python3终端打印customers表的第一行容----------------
sql_create = "create table customers_bpk(id int,name varchar(20),\
age int,address char(25),salary float);"
cursor.execute(sql_create)
#----------------以上:在mysql中创建新表customers_bpk--------------------
sql_insert = "insert into customers_bpk select * from customers;"
cursor.execute(sql_insert)
#-----------以上:将表customers的内容复制到customers_bpk----------------
cursor.execute('select * from customers_bpk') #返回行数，检查用
#--------------------------------------------------------------------
data2 = cursor.fetchall() #抓取所有数据
print(data2) #打印所有的数据，行记录用元组存储,所有数据用元组存储
print(cursor.rowcount)
#-------rowcount是一个只读属性，返回执行execte()方法影响的行数---------
for row in data2: #row是大元组中的每一个小元组，行数据
id = row[0] #保存了每一个小元组中-行记录的第一个数据
name = row[1]
age = row[2]
address = row[3]
salary = row[4]
print(str(id),name,str(age),address,str(salary)) #数据转化为字符串输
#--------------------------------------------------------------------
db.commit() #提交到数据库执行，数据库中开始执行各个语句

db.close() #最后关闭数据库链接

=====================================================================
练习：
将customers表中工资大于所有顾客平均工资的人的姓名，工资，地址打印出来，用pymysql完成：
sql_select = 'select name,salary,address from customers where salary > (select avg(salary) from customers);'
cursor.execute(sql_select)
data = cursor.fetchall()
print(data)
for row in data:
name = row[0]
salary = row[1]
address = row[2]
print(name,str(salary),address)

执行结果：
xioayu 60000.0 shenzhen
xiaohu 90000.0 guangzhou
Ann 30000.0 zhuhai

阅读全文

0 0