- SHOW DATABASES;
- CREATE DATABASE db;
- SHOW DATABASES;
- DROP DATABASE db;
-
- USE db;
- SHOW TABLES;
- CREATE TABLE IF NOT EXISTS student(
- stu_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
- stu_name VARCHAR(20) NOT NULL DEFAULT '',
- stu_age INT NOT NULL DEFAULT 0,
- stu_birthday DATE,
- stu_salary FLOAT DEFAULT '0.1'
- #外键在这里修饰
- );
- DESCRIBE student;
-
- ALTER TABLE student ADD COLUMN stu_grade INT NOT NULL;
- ALTER TABLE student DROP COLUMN stu_grade;
-
- INSERT INTO student VALUES(1,'张三',23,'1991-01-23','');
- INSERT INTO student (stu_name,stu_age,stu_birthday) VALUES('李四',22,'1992-1-2');
- INSERT INTO student (stu_name,stu_age) VALUES('王五',22);
- UPDATE student SET stu_name='张阳阳' WHERE stu_id=1;
- UPDATE student SET stu_name='张阳阳',stu_age=23 WHERE stu_id=1;
- DELETE FROM student WHERE stu_id=1;
-
-
- CREATE TABLE IF NOT EXISTS teacher(
- tea_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
- tea_name VARCHAR(20) NOT NULL DEFAULT '',
- tea_age INT NOT NULL DEFAULT 0,
- tea_birthday DATE,
- tea_salary FLOAT DEFAULT '0.1'
- #外键在这里修饰
- );
- INSERT INTO teacher SELECT * FROM student;
- INSERT INTO teacher (tea_name,tea_age) SELECT stu_name,stu_age FROM student WHERE stu_age=22;
-
-
- #Mysql默认的查询方式是ASC(升序); 降序(DESC)
-
- SELECT * FROM student;
- SELECT * FROM student WHERE stu_age=22;
- SELECT stu_name,stu_age,stu_birthday FROM student WHERE stu_age=22;
- SELECT stu_name AS '学生姓名',stu_age AS '年龄' FROM student ORDER BY stu_age; #用AS将显示字段自定义
- SELECT * FROM student WHERE stu_name IN ('张三','王五'); #用IN限定范围查找
- SELECT * FROM student WHERE stu_age BETWEEN 0 AND 22; #用BETWEEN AND进行查找
- SELECT * FROM student WHERE stu_age>= 23; # 用比较测试符:(包括=,<>,<,<=,>,>=) 查询
- SELECT * FROM student WHERE stu_name like '%李%';
- SELECT * FROM student WHERE stu_name like '_李%';
- SELECT * FROM student WHERE stu_name IS NOT NULL # IS[NOT] NULL
- SELECT COUNT(*) FROM student;
- SELECT avg(stu_age) FROM student;
- SELECT max(stu_age) FROM student;
- SELECT min(stu_age) FROM student;
- SELECT * FROM student LIMIT X,Y; #X代表从哪个下标开始,Y代表从X开始,查询Y个数据
-
-
- CREATE TABLE employee(
- num INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- d_id INT NULL, #外键(department)
- name VARCHAR(20),
- age INT ,
- gender VARCHAR(20),
- homeaddr VARCHAR(50)
- )
- INSERT INTO employee VALUES
- (NULL, 1001, '张三', 26, '男', '北京市海淀区'),
- (NULL, 1001, '李四', 24, '女', '北京市昌平区'),
- (NULL, 1002, '王五', 25, '男', '湖南长沙市'),
- (NULL, 1004, 'Aric', 15, '男', 'England');
-
- CREATE TABLE department(
- d_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
- d_name VARCHAR(20) NOT NULL,
- function VARCHAR(50),
- address VARCHAR(50)
- )
- CREATE TABLE worker(
- id INT PRIMARY KEY AUTO_INCREMENT,
- num INT(10), #员工编号
- d_id INT(50), #部门号(外键)
- name VARCHAR(20), #姓名
- gender VARCHAR(10), #性别
- birthday DATE, #出生日期
- address VARCHAR(50), #家庭住址
- FOREIGN KEY(d_id) REFERENCES department(d_id)
- )
- INSERT INTO department VALUES(1004, '人力资源部', '管理员工的信息', '2号楼3层');
- INSERT INTO employee VALUES(NULL, 1003, '刘花', 28, '女', '吉林省长春市');
- INSERT INTO employee VALUES(NULL, 1006, '王晶', 22, '女', '吉林省通化市');
- SELECT num AS '雇员ID',name,age,gender,homeaddr,d_name,function,address FROM employee,department
- WHERE employee.d_id=department.d_id;
-
-
- SELECT num, name, employee.d_id,age,gender, d_name, function
- FROM employee LEFT JOIN department
- ON employee.d_id=department.d_id;
-
- SELECT num, name, employee.d_id,age,gender, d_name, function
- FROM employee RIGHT JOIN department
- ON employee.d_id=department.d_id;
-
- CREATE TABLE performance(
- id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
- e_num INT(10) NOT NULL UNIQUE,
- performance FLOAT NOT NULL DEFAULT 0
- ) DEFAULT CHARSET=utf8;
- INSERT INTO performance VALUES
- (NULL, 2, 2000),
- (NULL, 1, 100),
- (NULL, 3, 5000),
- (NULL, 5, 8000),
- (NULL, 6, 10000);
- SELECT num, name, employee.d_id,age,gender, d_name, function, performance
- FROM employee
- LEFT JOIN department
- ON employee.d_id=department.d_id
- LEFT JOIN performance
- ON employee.num=performance.id;
-
- SELECT num,name,employee.d_id,age,gender,d_name,function
- FROM employee,department
- WHERE employee.d_id=department.d_id
- AND age>=1
- ORDER BY age DESC;
-
-
-
-
- SELECT * FROM employee
- WHERE d_id IN(SELECT d_id FROM department);
-
- CREATE TABLE computer_stu(
- id INT PRIMARY KEY,
- name VARCHAR(20),
- score FLOAT
- ) DEFAULT CHARSET=utf8;
- INSERT INTO computer_stu VALUES(1001, 'lILY', 85);
- INSERT INTO computer_stu VALUES(1002, 'Tom', 91);
- INSERT INTO computer_stu VALUES(1003, 'Jim', 87);
- INSERT INTO computer_stu VALUES(1004, 'Aric', 77);
- INSERT INTO computer_stu VALUES(1005, 'Lucy', 65);
- INSERT INTO computer_stu VALUES(1006, 'Andy', 99);
- INSERT INTO computer_stu VALUES(1007, 'Ada', 85);
- INSERT INTO computer_stu VALUES(1008, 'jeck', 70);
-
- CREATE TABLE scholarship(
- level INT PRIMARY KEY,
- score INT
- ) DEFAULT CHARSET=utf8;
- INSERT INTO scholarship VALUES(1, 90);
- INSERT INTO scholarship VALUES(2, 80);
- INSERT INTO scholarship VALUES(3, 70);
-
- SELECT com.id,com.name,com.score FROM computer_stu as com
- WHERE score>=(SELECT score FROM scholarship WHERE level=1);
-
-
- SELECT dep.d_name FROM department as dep
- WHERE dep.d_id IN(SELECT emp.d_id FROM employee as emp WHERE emp.age!=24);
-
-
- SELECT * FROM employee
- WHERE EXISTS(SELECT * FROM department WHERE d_id=1003);
- SELECT * FROM employee as emp
- WHERE emp.age>0 AND EXISTS (SELECT * FROM department WHERE d_id=1003);
-
-
-
- SELECT * FROM computer_stu as coms
- WHERE coms.score>= ANY(SELECT score FROM scholarship) ORDER BY score DESC;
-
- SELECT * FROM computer_stu as coms
- WHERE coms.score>=ALL(SELECT score FROM scholarship)
-
-
- UNION和UNION ALL关键字都是将两个结果集合并为一个
-
- UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果;
- UNION ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了
-
-
- 该关键字是去掉某个属性的重复操作,或者是去重操作后的计数,而且返回值只能是这一个属性的结果集,如果返回结果集里面含有多个字段,将是对多个属性同时起作用(也就达不到这个关键字的初衷),
- 详细的看 http:
-
- INSERT INTO product VALUES
- (1005, '头疼灵1号', '治疗头疼', 'DD制药厂', '北京市房山区');
- REPLACE INTO product VALUES
- (1005, '头疼灵1号_replace', '治疗头疼', 'DD制药厂', '北京市房山区');
-
-
- mysql -h(IP) -u(用户名) -p(密码)
-
- mysqladmin -u用户名 -p旧密码 password 新密码
-
-
- grant update,select,insert,delete on *.* to zyy1@"%" identified by "zyy1";
- grant all on *.* to zyy2@"%" identified by "zyy2";
- grant update,select,insert on *.* to zyy3@"%" identified by "zyy3";
- grant update,select,insert,delete on db.* to zyy4@localhost identified by "zyy4";
-
- mysqldump -hlocalhost -uroot -p123456 db>C:\\db.sql
- mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
- mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql
- mysqldump -–add-drop-table -uusername -ppassword databasename > backupfile.sql
- mysqldump -hhostname -uusername -ppassword databasename | gzip > backupfile.sql.gz
- mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
- mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql
- mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
- mysqldump –all-databases > allbackupfile.sql
- mysql -hhostname -uusername -ppassword databasename < backupfile.sql
- gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
- mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename
-
-
-
-
- 索引介绍:http:
- SHOW INDEX FROM app_account(表名称)
-
-
-
-
-
-
-
-
-
-
- 查看外键:
- SHOW CREATE TABLE subscriber(表名称)
-
- 显示如:
- CREATE TABLE `subscriber` (
- `subscriber_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订阅者身份标识',
- `app_account_id` int(11) NOT NULL COMMENT 'app_account表主键',
- `subscribe_status` char(1) NOT NULL COMMENT '订阅状态 0 关闭 1开启',
- `push_url` varchar(256) DEFAULT NULL COMMENT '业务推送地址',
- `business_flag` char(1) DEFAULT NULL COMMENT '业务标识',
- `note` varchar(256) DEFAULT NULL COMMENT '备注',
- `subscriber_name` varchar(64) NOT NULL COMMENT '订阅者名称',
- PRIMARY KEY (`subscriber_id`),
- KEY `subscription_info_app_account` (`app_account_id`),
- CONSTRAINT `subscriber_ibfk_1` FOREIGN KEY (`app_account_id`) REFERENCES `app_account` (`app_account_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='订阅者表'
-
- 删掉该外键:
-
- ALTER TABLE subscriber DROP FOREIGN KEY subscriber_ibfk_1; #记录
-
- 添加外键:
-
- ALTER TABLE subscriber ADD CONSTRAINT FK_ID FOREIGN KEY(app_account_id) REFERENCES app_account(app_account_id)
-
-
- show TRIGGERS
-
-