第16次预习课-数据库mysql-20181024
第16次预习课 mysql 20181024
>mysql –uroot –p #默认是3306端口，IP是localhost
CREATE DATABASE IF NOT EXISTS testnewman DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
#大小写不敏感，不存在则创建，存在不做操作
新建student表：
create table student(
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(20),
sex varchar(6),
submission_date date,
primary key(id)
)engine=innodb DEFAULT CHARSET = utf8;
mysql> select * from student where sex="female" limit 1; #限制只查询一条数据
mysql> select * from student where sex="female" limit 1,1; #第一个结果的后面再取1条
select count(*),sex from student where id>1 group by sex having count(*)=1;
mysql> select * from student where id in (1,2); # in 的用法
mysql> select * from student where id in (select * from id where id>2); #用了两个表，student和id。 子查询。
Id表：
mysql> select * from id;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
5 rows in set (0.00 sec)
新建两个表：
create table A(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) not null,
sex varchar(6),
salary varchar(20),
department_id int,
primary key(id)
)engine=innodb DEFAULT CHARSET = utf8;
create table department(
id int NOT NULL AUTO_INCREMENT,
department_name varchar(20) not null,
primary key(id)
)engine=innodb DEFAULT CHARSET = utf8;
插入数据：
insert into A values(1,"jone","male",100,1);
insert into A values(2,"Jane","female",60,2);
insert into A values(3,"Jason","female",50,3);
insert into A values(4,"Jordan","male",20,10);
insert into department values(1,"Hr");
insert into department values(2,"IT");
insert into department values(3,"OP");
insert into department values(4,"Finance");
内连接 （不推荐使用，会跳过索引，查询全盘慢）
select person.name,depart.department_name from A as person inner join department as depart on person.department_id = depart.id;
左连接
select person.name,depart.department_name from A as person left join department as depart on person.department_id = depart.id;
mysql> select person.name,depart.department_name from A as person right join dep
artment as depart on person.department_id = depart.id;
select person.*,depart.* from A as person right join department as depart on person.department_id = depart.id; #用星号代表取所有字段
Union 将两个查询结果合并，自动过滤重复的
select id from A
union
select id from department;
Union all 将两个查询结果合并，不自动过滤重复的
select id from A
union all
select id from department;
import pymysql
import random
def insertData():
conn = pymysql.connect(
host = "localhost",
port = 3306,
user = "root",
passwd = "123456",
db = "testnewman",
charset = "utf8")
cur = conn.cursor()
conn.select_db('testnewman')
courseList = ['python', 'java', 'mysql', 'linux', '接口测试', '性能测试', '自动化测试','数据结构与算法']
for i in range(1, 101):
student_id = '201803' + '0' * (3 - len(str(i))) + str(i)
name = random.choice(['Lucy','Tom','Lily','Amy','Dave','Aaron','Baron']) + str(i)
tel = '1' + str(random.choice([3, 5, 7, 8])) + str(random.random())[2:11]
sex = random.choice(['女', '男'])
stuinfo_sql = "insert into studentInfo(student_id, name, sex, tel, AdmissionDate) \
values('%s', '%s', '%s', '%s', date_sub(now(),interval %s day))" \
%(student_id, name, sex, tel, random.randint(90, 120))
cur.execute(stuinfo_sql)
conn.commit()
for j in courseList:
grade_sql = "insert into grade(stuID,course,score) values('%s','%s',%s)" %(student_id,j,random.randint(80, 100))
cur.execute(grade_sql)
conn.commit()
cur.close()
conn.commit()
conn.close()
insertData()
print("数据插入结束！")
create table studentInfo(
ID int not null auto_increment comment "不为空的自增长的主键ID",
student_id varchar(20) not null,
name varchar(30) not null,
sex char(4),
tel varchar(13) unique not null,
AdmissionDate datetime default '0000:00:00 00:00:00',
primary key (ID),
unique student_id(student_id) #唯一键索引名(列名)
)engine=innodb character set utf8 comment "学生信息表";
create table grade(
ID int auto_increment not null,
stuID varchar(20),
course varchar(20) not null,
score tinyint(4) default 0,
primary key (ID),
key idx_stuid(stuID), #主键
CONSTRAINT FK_ID FOREIGN KEY(stuID) REFERENCES studentInfo(student_id)
#外键
)engine=innodb character set utf8 comment "学生成绩表";
