MySQL的一些基本命令笔记(4)

delete 语句嵌套子查询：

delete from 表名1 where 列名 操作符 (select 列名 from 表名2 where 条件);

示例：
delete from customers_1 where salary > (select avg(salary) from customers_1);[删除一个之后还会在重新计算avg（salary）之后再重新执行执行该语句]
delete from customers_1 where age in (27,32);
***delete from customers_2 where salary > (select avg(salary) from customers_1); 这个可以用

update customers_2 set salary=salary*2 where age > (select avg(age) from customers_1)

SQL函数
1.SQL aggregate 聚合函数
avg():average   平均值  
count():        行数
max():          最大值 
min():          最小值
sum():          求和

2.SQL Scalar 标量函数
ucase():upper case   以大写字母显示
lcase():lower case   以小写字母显示
length():            求字符长度  
round():             对某个数字字段进行指定小数位数的四舍五入
now():               返回当前的系统日期和时间
format():            格式化某个字段的显示方式

思考题：
1.求出customers表中，地址为北京的顾客的总收入
select * from customers where address ="beijing" and (select sum(salary)from customers );
select sum(salary) from customrs where address="beijing";

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

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

4.列出customers表中，每个顾客工资与所有顾客的平均工资
提示分两步做
select avg(salary)from customers;先求出平均工资的数然后在
select name，salary-[上面求出的数字] from customers;
select name,salary -(select avg(salary) from customers) as difference
                  [是把salary -(select avg(salary) from customers) 重命名为didderence] from customers;


mid(列名，起始位置，长度)
substring(列名，起始位置，长度)
以上两个语句在这个版本是没有啥区别的

select mid (name,1,3) from customers;
1.从起始位置为1开始
2.打印出三个字母的长度
注意：第一个字母的位置为1，不是0

***as 别名;
select formate (列名，新的格式) from 表名；
select date_format(now(),"%Y-%M-%D") as date from customers;

Union：综合多个select语句，且返回不重复的行
如果要返回重复的行用union all
注意：
1.每个select语句中必须选中相同数量的列
2.列的数目要一致
3.列的数据类型要一致
4.列的顺序要一致

例如：
选中customers表中，年龄小于25岁和大于27岁的人的姓名和年龄
1.select * from customers where age <25 or age>27;

2.select * from customers where age <25 union select * from customers where age>27;

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

alter table  向表中添加一个新的列
 
alter table 表名 add 列名 数据类型
alter table customers add gender char(20);
alter table customers add gender char(20) default "F";


向表中删除一个列
alter table 表名 drop column 列名;
alter table customers drop column gender; 

修改表中某列的数据类型
alter table 表名 modify  column 列名 新的数据类型

alter table customers modify column  salary float;

      微观修改表中的元素[改变行]    宏观修改表中的元素[改变列]
修改  update                          alter ..modify column..
删除  delete                          alter ..drop column.. 
添加  insert                          alter ..add column.. 

对表取消主键限制
alter table 表名 drop primary key;

show creat table courses;
alter table courses drop primary key;
show create table corses; 

对表添加主键限制
alter table 表名 add primary key(列名)；
alter table customers add primary key(ID);

对表取消外键限制：
alter table 表名 drop foreign key 列名；
alter table courses drop primary key instructor;

对表添加外键限制
alter table 表名 add foreign key (列名) references 表名(列名)；

对表添加非空限制：
alter table 表名 modify 列名  数据类型 not null；
alter table courses modify name varchar(20) not null;

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

*********
truncate table:保留表头，也即保留表的格式，只删除表中存放的数据内容
truncate table 表名；
truncate table customers_1;
delete from  customers_1;[和truncate table功能一样，一般常与where条件语句一起使用]

drop table customers_1: 连表头带表中的内容全部删除

思考题：
查询customers表中，城市的平均工资大于3000的城市名和工资信息
select address，salary,avg(salary) from customers having avg>3000 where (select address from customers); 

学生疑问1；
删除外键必须用show create table 查看系统自动生成的外键名（如：courses_ibfk_1,而不是instructor），用该生成的新名字来删除外键，例如
alter table courses drop foreign key courses_ibfk_1;
alter table courses add foreign key(instructor) references teachers(name); 

学生疑问2 
alter table 表名 rename 新表名；
alter table customers rename customers_update;

学生疑问3
alter table 表名 change 旧名字 新名字 数据类型；
alter table courses change ID c_id int；

执行事务：
   事务机制：确保数据一致性
   事务的4个属性：
            1.原子性：一个事务是一个不可分割的工作单位，事务中包括的各个操作要么都做，要么都不做
            2.一致性：事务必须是数据库从一个一致性状态变到另一个一致性状态
            3.隔离性：一个事务的执行不能被其他并发的事务打扰
            4.持久性：一个事务一旦提交，它对数据库中数据的改变就应该是永久性的 
注意：
1.MYSQL中默认aql语句的结果自动commit到数据库
2.开始需要写start transaction，这样自动commit就会被禁用，知道我们使用commit或rollback终止这个transaction    
start transaction
sql1
sql2
...
sqln
commit;
示例：
start transction; 
insert into teachers values("wei","m","instructor");
insert into teachers values("wang","f","instructor");
select * from teachers;
commit; 

回滚的演示：
start transction; 
insert into teachers values("han","m","instructor");
rollback;[删除上面的数据]

python3与mysql
pysql库：

import pymysql
#打开数据库链接(commect链接)
db = pymysql.connect("localhost","root","123456","testDB")
#使用cursor()方法创建一个游标对象cursor
cursor =db.cursor()
#使用execute()方法执行sql查询返回值为表中所有记录的总数
SQL="select * from customers;"
cursor.execute(SQL) 
#等价于cursor.execute("select * from customers;")
#使用fetchone()方法获得单条数据
data =cursor.fetchone()#[相当于迭代器只打印一行，超出行数不会报错也啥都不显示]
#注意，返回的data是保存在元组tuple中，用()包起来
print ("The first row in the table is:",data)

sql = "drop table customers_bkp;"#是将生成的表格进行删除，如果不删除再次运行表格已经存在就会报错

cursor.execute(sql)
#写sql语句，创建一个新的表格，叫customers_bkp包括id,name,age,address,salary,gender
SQL_CREATE="create table customers_bkp(\
Id int,\
name varchar(20),\
age int,\
address char(25),\
salary float\
);"

#使用execute()方法执行上面的sql语句
cursor.execute(SQL_CREATE)

#写sql语句，像customers_bkp表中插入行，完成对customers表内容的复制
#提示insert into ...select * from .....
SQL_INSERT="insert into customers_bkp\
(id,name,age,address,salary)\
select * from customers;"

cursor.execute(SQL_INSERT)#[插入表格之后还的执行之后才会出现上面语句的结果]


#使用execute()方法执行上面的sql语句
cursor.execute("select * from customers_bkp;")

#fetchall()接收全部的返回结果，即表中所有的行，返回结果存在元组中
data2 = cursor.fetchall()#[把上面的表格数据以元组形式返回]
#rowcount是一个只读属性，返回执行execute()方法影响的行数
print(cursor.rowcount)#[返回执行execute()的行数]
#打印
for row in data2:
    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表中工资大于所有顾客的平均工资的人的姓名和工资还有地址打印出来
要求利用nysql库完成
select name,address,salary from customers where salary>(select avg(salary) from customers);

import pymysql
db=pymysql.connect("localhost","root","123456","testDB")
c =db.cursor()
sql="select name,address,salary from customers where salary>(select avg(salary) from customers);"
c.execute(sql)
d=c.fetchall()
# print(d)
for x in d:
    name=x[0]
    address=x[1]
    salary=x[2]
    print(name,address,salary)
db.commit
db.close()    


******select address,sum(salary) from customers group by address having address in("beijing","shenzhen");