MySQL Learning notes (i)

#if exists If a result set exists, the IF is the where condition if true executes
DROP TABLE IF EXISTS itcast_class;
#创建表格
CREATE TABLE Itcast_class (
class_id INT PRIMARY KEY auto_increment,
Class_name VARCHAR (Ten) not NULL DEFAULT ' '
) CHARACTER SET UTF8;
#添加字段
ALTER TABLE itcast_class ADD stu_id INT;
#删除字段
ALTER TABLE itcast_class DROP COLUMN stu_id;
#添加数据
INSERT into Itcast_class VALUES (NULL, ' Class Four ');
#修改数据
UPDATE itcast_class SET stu_id = 1 WHERE stu_id is NULL;
#查询表格
SELECT * from Itcast_class;
#查询表结构
SHOW CREATE TABLE Itcast_class;

CREATE TABLE Itcast_student (
stu_id INT PRIMARY KEY auto_increment,
Stu_name VARCHAR (Ten) is not NULL,
class_id INT,
#创建外键, general items do not use foreign keys, only logical associations
FOREIGN KEY (class_id) REFERENCES Itcast_class (class_id)
) CHARACTER SET UTF8;

INSERT into Itcast_student VALUES (NULL, ' Zhao Liu ', 4);

SELECT * from Itcast_student;

CREATE TABLE Itcast_teacher (
teacher_id INT PRIMARY KEY auto_increment,
Teacher_name VARCHAR (Ten) is not NULL,
Teacher_days INT not null# class days
) CHARACTER SET UTF8;

CREATE TABLE Itcast_cla_tea (
ID INT PRIMARY KEY auto_increment,
class_id INT not NULL,
teacher_id INT not NULL
) CHARACTER SET UTF8;

SELECT * from Itcast_cla_tea;

SELECT * from Itcast_teacher;

#存储引擎即是表类型, different types, the speed of storage data will have an impact, the default InnoDB, the only foreign key support engine, in the MySQL installation directory
#my configuration in. ini files
#basedir = "D:\ProgramFiles\Mysql"
# DataDir = ...
#datadir = "D:\ProgramFiles\Mysql\data"
# Database Engine
#default-storage-engine=innodb
# set MySQL encoding
#character_set_server =utf8
# setting MySQL Character set
#collation-server=utf8_general_ci

#分组和排序
SELECT * from Itcast_student GROUP by class_id ORDER by stu_id DESC;
SELECT * from Itcast_student ORDER by class_id,stu_id DESC;

#limit, limit the number of query bars, that is, paging, he has two conditions, offset offset, that is, the starting index, the default is the total number of records 0,row_count query, that is, the length
SELECT * from Itcast_student LIMIT 0, 4;

#distinct去掉重复记录, note: Duplicate values are all fields that you query are the same, not one field
#与之相对的是all, default
#数据库日志系统用的较多, an error occurs multiple times.
SELECT DISTINCT * from Itcast_student;

#union联合查询, merging multiple select query Result sets NOTE: The result set of the Union operation is eliminated if there is duplicate data, similar to distinct
#union all will query all
When #子语句order by sort
#配合limit才会生效, when a union is making a child statement, an order by that has no limit clause is invalidated (optimized)
(SELECT * from itcast_student WHERE class_id = 1 ORDER by stu_id DESC LIMIT 1)
UNION
(SELECT * from itcast_student WHERE class_id = 3 ORDER by stu_id DESC LIMIT 1)
#子查询
SELECT * from Itcast_teacher WHERE teacher_days = (SELECT MAX (teacher_days) from Itcast_teacher);
#带过班级三的老师带过那些班级?
SELECT * from Itcast_class WHERE class_id in (
SELECT class_id from Itcast_cla_tea WHERE teacher_id in (
SELECT teacher_id from Itcast_cla_tea WHERE class_id =
(SELECT class_id from itcast_class WHERE class_name = ' class three '));

#和in同样的运算符any, All,some and any, just foreign syntax to reconcile their programmers ' understanding and appearance
#=any () and in
#!=any () is not equivalent to not in, it is seldom used in actual development, because as long as the value is not equal to any number in the set, it is generally found that all
#=all () is equal to all in the collection, usually a value is not found
The #!=all () value is not equal to any of the sets, and all values outside the set are isolated
#行子查询 select * FROM table name where (field 1, field 2 ...) = (select (Field 1, Field 2 ...) from table name);
#表子查询
SELECT * FROM [select * from Itcast_student where stu_id >] as temp where stu_name like ' li% ';

#表名查询
SHOW TABLES;

#exists是否存在
SELECT * from Itcast_class as CLA WHERE EXISTS;
(SELECT * from Itcast_cla_tea WHERE cla.class_id = class_id);

CREATE TABLE Join_teacher (
ID INT PRIMARY KEY auto_increment,
T_name VARCHAR (20),
Gender enum (' Male ', ' female ', ' secret ') #性别, using enum type
) ENGINE INNODB CHARACTER SET UTF8;

INSERT into Join_teacher VALUES
(1, ' Hansung Motel ', ' Male '),
(2, ' Li Bai ', ' female '),
(3, ' Han Fei zi ', ' secret '),
(' 4 ', ' Sun Wu ', ' Male ');

SELECT * from Join_teacher;

CREATE TABLE Join_class (
ID INT PRIMARY KEY auto_increment,
C_name CHAR (5),
The guest-CHAR (3)
) ENGINE INNODB CHARACTER SET UTF8;

#修改字段类型
ALTER TABLE join_class MODIFY COLUMN c_name CHAR (7);
SHOW CREATE TABLE Join_class;

INSERT into Join_class VALUES
(1, ' php0115 ', ' 207 '),
(2, ' php0228 ', ' 104 '),
(3, ' php0331 ', ' 102 '),
(4, ' php0505 ', ' 202 ');

SELECT * from Join_class;

CREATE TABLE Join_teacher_class (
ID INT PRIMARY KEY auto_increment,
t_id INT,
C_ID INT,
Days TINYINT,
Begin_date date,
End_date Date
) ENGINE INNODB CHARACTER SET UTF8;

INSERT into Join_teacher_class VALUES
(1,1,1,15, ' 2013-01-15 ', ' 2013-02-20 '),
(2,1,2,18, ' 2013-02-28 ', ' 2013-03-30 '),
(3,1,3,22, ' 2013-03-31 ', ' 2013-05-05 '),
(4,2,1,20, ' 2013-02-22 ', ' 2013-03-25 '),
(5,2,2,22, ' 2013-03-31 ', ' 2013-04-29 '),
(6,3,1,15, ' 2013-03-27 ', ' 2013-04-18 '),
(7,1,1,15, ' 2013-04-19 ', ' 2013-05-01 '),
(8,3,3,15, ' 2013-05-28 ', ' 2013-06-15 '),
(9,2,1,5, ' 2013-05-04 ', ' 2013-05-15 ');

SELECT * from Join_teacher_class;

#内连接, the connection data does not exist and does not appear
SELECT t.t_name,t_c.days from Join_teacher as T INNER join Join_teacher_class as T_c
on t.id = t_c.t_id;

#交叉连接, that is, the Cartesian product
SELECT t.t_name,t_c.days from Join_teacher as T Cross join Join_teacher_class as T_c;

#on和where都是条件筛选, but on is determined when the data forms a link, does not connect without passing, and where is the data formed after the Cartesian product judgment screening
#简单说on是判断后形成新表, where is the new table after the formation of judgment, logical meaning is not the same
SELECT t.t_name,t_c.days from Join_teacher as T INNER join Join_teacher_class as T_c
On t.id = t_c.t_id WHERE t_c.days>15;

#using快捷语法, this is only a grammar test, that is, the IDs of the two tables will match automatically, and actually will match the foreign key of a table, and their fields must be the same
SELECT t.t_name,t_c.days from Join_teacher as T INNER join Join_teacher_class as T_c
USING (ID);

#外连接, the connection data does not exist and will appear empty
SELECT t.t_name,t_c.days from Join_teacher as T left OUTER join Join_teacher_class as T_c
On t.id = t_c.t_id

SELECT t.t_name,t_c.days from Join_teacher as T right OUTER join Join_teacher_class as T_c
On t.id = t_c.t_id

#自然连接NATURAL, it will find the same field as the ID match, inside and out
SELECT * from Join_teacher NATURAL join Join_teacher_class;

#查询数据导入硬盘, save data backup
#默认字段之间用 ' \ t ', between lines with ' \d '
SELECT null,stu_name,class_id to OUTFILE ' c:/users/administrator/desktop/itcast_student ' from itcast_student;

#导入数据
LOAD DATA INFILE ' c:/users/administrator/desktop/itcast_student ' into TABLE itcast_student;

#修改默认分隔
SELECT * into OUTFILE ' c:/users/administrator/desktop/itcast_student '
Fields TERMINATED by ' \ t ' enclosed by ' \ ' #字段分隔
LINES TERMINATED by ' \ n ' starting by ' Start: ' #行分隔
From Itcast_student;

#二进制导出
#测试几次, it seems that you can only export one piece of data that is not delimited
SELECT * into DumpFile ' c:/users/administrator/desktop/itcast_student ' from itcast_student LIMIT 1;

#不同插入
INSERT into itcast_student SET stu_name = ' Wang Fuzi ', class_id = 2;

#当插入主键冲突时可以改为修改
INSERT into Itcast_student VALUES (2, ' leper ', 2)
On DUPLICATE KEY UPDATE
Stu_name= ' leper ', class_id=2;

#REPLACE ==on DUPLICATE KEY UPDATE
REPLACE into Itcast_student VALUES (3, ' convective Clouds ', 3);

#查询插入 * Worm Replication
INSERT into Itcast_student (stu_name,class_id) SELECT stu_name,class_id from Itcast_student;

SELECT * from Itcast_student;

DESC itcast_student;

SELECT * from Itcast_student;
SELECT * from Itcast_class;
SELECT * from Itcast_cla_tea;

#多表删除, delete all relevant details of the class
SELECT * FROM
(SELECT c.*,t.stu_id,t.stu_name from Itcast_class as C JOIN itcast_student as T
On c.class_id = t.class_id WHERE c.class_name= ' Class One ') as Temp
JOIN Itcast_cla_tea as c_t on temp.class_id = c_t.class_id;

SHOW CREATE TABLE itcast_student;
#删除外键, when the foreign key name is not written, the system defaults: Table name _ibfk_n (n for the first foreign key)
ALTER TABLE itcast_student DROP FOREIGN KEY itcast_student_ibfk_1;

#两张表删除
DELETE from Itcast_class,itcast_student USING
Itcast_class JOIN itcast_student
On itcast_class.class_id = itcast_student.class_id WHERE itcast_class.class_name= ' class One '

SELECT * FROM
(SELECT c.*,t.stu_id,t.stu_name from Itcast_class as C JOIN itcast_student as T
On c.class_id = t.class_id WHERE c.class_name= ' Class two ') as Temp
JOIN Itcast_cla_tea as c_t on temp.class_id = c_t.class_id;

#三张表删除
DELETE from Itcast_class,itcast_student,itcast_cla_tea USING
Itcast_class JOIN itcast_student
On itcast_class.class_id = itcast_student.class_id JOIN Itcast_cla_tea
On itcast_cla_tea.class_id = itcast_class.class_id
WHERE itcast_class.class_name= ' Class II '

#TRUNCATE删除表, creating tables does not log information from the original table, most typically the zero-based index
#delete逐行删除, save some information of the original table, such as the index unchanged

#多表更新
UPDATE tab1 JOIN tab2 on tab1.a = tab2.a SET tab1.b = ', tab2.c= ' where tab1.id= ';

#备份还原
#1, the simplest way is to find the corresponding table data (frm) directly into the MySQL folder, table structure (myd), table storage Engine (MYI) three files packaged
#需要时将他们解压到指定的数据目录下
#注: The table under the default InnoDB storage engine executes this method, you can see the table name using show tables, but you cannot use the

#2, Backup: Execute mysqldump-uroot-p Test > C:/users/administrator/desktop/test.sql directly in Mysqld.exe in CMD
#还原: Source c:/users/administrator/desktop/test.sql or Execute SQL command directly in SQL file
#而在sqlYong编辑器中在最为方便简单, just do the right things in the tool options

#s视图
CREATE TABLE Info_teacher (
ID INT PRIMARY KEY auto_increment,
T_name VARCHAR (10),
Salary DECIMAL (10,2)
);

INSERT into Info_teacher VALUES
(NULL, ' Confucius ', 100.45),
(NULL, ' Lao Tzu ', 200.10),
(NULL, ' ions ', 155.44);

SELECT * from Info_teacher;

CREATE VIEW V_teacher as SELECT id,t_name from Info_teacher;
INSERT into V_teacher VALUES (NULL, ' Manulife ');

SELECT * from V_teacher;

#视图的理解, which is the encapsulation of query statements, simplifies some complex business logic
#内部执行过程有两种, 1, merge execution mode, that is, query to apply subqueries
#2, a temporary table, a view as a temporary table, and then manipulate it

#事务
CREATE TABLE Zj_school (
ID INT PRIMARY KEY auto_increment,
Cz_money DECIMAL (10,2) COMMENT ' Banfei '
) ENGINE INNODB CHARACTER SET UTF8;

INSERT into Zj_school VALUES (null,1250);
SELECT * from Zj_school;

CREATE TABLE Zj_student (
ID INT PRIMARY KEY auto_increment,
Stu_money DECIMAL (10,2) COMMENT ' living expenses '
) ENGINE INNODB CHARACTER SET UTF8;

INSERT into Zj_student VALUES (null,5000);
SELECT * from Zj_student;

#班费30
UPDATE zj_student SET Stu_money = stu_money-30 WHERE id = 1;
UPDATE zj_school SET Cz_money = Cz_money + WHERE id = 1;

#是否提交
SET autocommit = 1;
SHOW VARIABLES like ' autocommit ';
#成功commit
#失败rollback一般不用这种方式操作事务 that can affect other operations

#开启事务
START TRANSACTION;
#提交
COMMIT;
#事务在innodb或者DBD两种存储引擎中生效

#1, atomicity, i.e. success or failure at the same time
#2, consistency, you open transactions in a database connection, and if you don't, you can operate on other connections, but once you
#开启事务的连接中操作了某张表而没有提交, the table cannot be manipulated in another connection at this time
#3, isolation, transactions do not affect each other
#4, persistence, once submitted, permanent (acid transaction features)

#触发器
#Create Trigger name Event execution code
#触发不包括查询, the event keyword three kinds of insert,delete,update, the timing of the two after,before, combining a total of six kinds of events
#old和new获取事件操作的数据
#insert不能使用old
#delete不能使用new

#1, triggers cannot have the same name
#2, currently MySQL supports only one type of event setting a trigger
CREATE TRIGGER pay_cost after UPDATE on zj_student
For each ROW
UPDATE zj_school SET Cz_money = Cz_money + 20;

UPDATE zj_student SET Stu_money = stu_money-40 WHERE id = 1;

SHOW CREATE TRIGGER pay_cost;

DROP TRIGGER Pay_cost;

CREATE TRIGGER pay_cost after UPDATE on zj_student
For each ROW
UPDATE zj_school SET Cz_money = Cz_money + (Old.stu_money-new.stu_money);

#如果多条sql语句, you need to use special characters
DELIMITER $$
CREATE TRIGGER Ruxue after INSERT on zj_student
For each ROW
BEGIN
UPDATE zj_school SET stu_count = stu_count+1;
UPDATE zj_school SET Stu_money = stu_money+20;
END
$$

DELIMITER;

#sql编程
SELECT now ();
SHOW VARIABLES like ' char% ';
#定义变量set variable name = variable value; To differentiate between system and custom variables, you need to precede the variable with the @
SET @who = ' Li think ';
SELECT @who;
#into注入
SELECT 10,20,30 into @a,@b,@c; #该方式可以查询注入
SELECT @who: = ' Xiao Li '; #赋值
SELECT @who = ' big li '; #判断语句

#变量有效期: Connection interrupted, scope: User-defined is global, function is local
SELECT @a,@b,@c;

SELECT RAND (); #1到0之间的随机数, MySQL various built-in functions own Baidu understand
SELECT From_unixtime (Unix_timestamp ());
SELECT now ();
SELECT LENGTH (' SDBASJD ');
SELECT LENGTH (' number of bytes '); #utf8三字节
SELECT char_length (' Number of characters ');
SELECT SUBSTRING (' Good voice of China ', 3,3); #位置从1开始
SELECT lpad (' 1 ', 4, ' 0 '); #补足位数, start from the left.
SELECT rpad (' 2 ', 3, ' 0 '); #右边开始
SELECT MD5 (' 1 '); #加密函数
SELECT PASSWORD (' 1 ');

#自定义函数 (store function) Create function function name (argument list) return value type function body

DELIMITER $$
CREATE FUNCTION SayHello () RETURNS VARCHAR (20)
BEGIN
RETURN ' Hello world! ';
END
$$

DELIMITER;

SELECT SayHello ();

#sql中的流程控制
DELIMITER $$
CREATE FUNCTION fun1 () RETURNS VARCHAR (20)
BEGIN
IF HOUR (now ()) >
RETURN ' late ';
ELSE
RETURN ' early ';
END IF;
END
$$

DELIMITER;

SELECT fun1 ();

DROP FUNCTION IF EXISTS fun2;
DELIMITER $$
CREATE FUNCTION fun2 () RETURNS INT
BEGIN
SET @i = 1;
SET @sun = 0;
While @i <=
SET @sun = @sun + @i;
SET @i = @i + 1;
END while;
RETURN @sun;
END
$$

DELIMITER;

SELECT fun2 ();

#循环提前终止, exit loops are different from Java, not by location, but by tags.
#leave equivalent to break
#iterate equivalent to continue

DELIMITER $$
CREATE FUNCTION fun3 () RETURNS INT
BEGIN
SET @i = 1;
SET @sun = 0;
W:while @i <=
IF @i = 5 Then
LEAVE W;
END IF;
SET @sun = @sun + @i;
SET @i = @i + 1;
END while W;

RETURN @sun;
END
$$

DELIMITER;

SELECT Fun3 ();

DELIMITER $$
DROP FUNCTION IF EXISTS fun4;
CREATE FUNCTION fun4 (u_name varchar) RETURNS varchar (20)
BEGIN
RETURN CONCAT (' Hello ', u_name);
END
$$

DELIMITER;

SELECT fun4 (' hello ');

SHOW CREATE FUNCTION Fun4;

#where和having一样, but the where condition acts on the table, and having the result set after the query
SELECT * from itcast_student WHERE class_id = 3 have stu_name = ' convective clouds ';

#四大索引
#普通索引 (Index)
#主键索引 (primary key)
#唯一索引 (Unique)
#全文索引 (Fulltext)

MySQL Learning notes (i)