mysql単一テーブル操作
7503 ワード
1. ：
： goods
： utf8 -- UTF-8 Unicode
： utf8_general_ci
( )： CREATE DATABASE goods;
2. : DROP DATABASE goods;
3.
CREATE TABLE employee(
dept VARCHAR(50),
sno VARCHAR(50),
NAME VARCHAR(50),
age INT,
gender CHAR(2),
entry_time DATE
)
3.1
student (sno,sname)
course (cno,cname)
sc (sno,cno,score)
sno，cno sno， cno
sql ：
ALTER TABLE tb_active ADD CONSTRAINT FK_ID FOREIGN KEY(user_id) REFERENCES tb_user(id)
user_id id
ALTER TABLE sc ADD CONSTRAINT FK_ID FOREIGN KEY(sno) REFERENCES student(sno)
sno sno
ALTER TABLE sc ADD CONSTRAINT FK_ID2 FOREIGN KEY(cno) REFERENCES course(cno)
4.. : DROP TABLE
5.
INSERT INTO employee
(dept,sno,NAME,age,gender,entry_time)
VALUES
(' ','100010',' ',25,' ','2008/04/01');
6.
DELETE FROM employee
WHERE NAME=' ';
7.
UPDATE employee
SET dept=' ',age=20
WHERE NAME=' ';
8.
SLEECT ：
SELECT
FROM
[WHERE 1]
[GROUP BY 1] [HAVING 2]
[ORDER BY 2[ASC|DESC]]
' ' ；
' ' , ；
' 1' ；
GROUP BY:' 1' ；
ORDER BY:' 2'
'ASC' ， ；( )
'DESC' 。( )
GROUP BY COUNT()、SUM() 。
：
CREATE TABLE employee(
num INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
d_id INT NULL,
name VARCHAR(20),
age INT,
gender VARCHAR(4),
homeaddr VARCHAR(50)
)
1.
SELECT num, d_id, name, age, gender, homeaddr FROM employee;
SELECT * FROM employee;
2.
SELECT num, name, gender, homeaddr FROM employee;
3.
SELECT * FROM employee WHERE d_id=1001;
4. IN
IN 。
：
[NOT] IN( 1, 2,..., n)
=, , >=, !=, <>, !>, !<
BETWEEN AND, NOT BETWEEN AND
IN, NOT IN
LIKE, NOT LIKE
IS NULL, IS NOT NULL
AND, OR
（1） IN 。
SELECT * FROM employee WHERE d_id IN(1001,1004);
（2） NOT IN , 。
SELECT * FROM employee WHERE name NOT IN(' ',' ');
5. BETWEEN AND
BETWEEN AND
：
[NOT] BETWEEN 1 AND 2
（1） BETWEEN AND ， age 15 25。
SELECT * FROM employee WHERE age BETWEEN 15 AND 25;
（2） NOT BETWEEN AND employee 。 age 15 25 。
SELECT * FROM employee WHERE age NOT BETWEEN 15 AND 25;
6. LIKE
LIKE
：
[NOT] LIKE ' '
SELECT * FROM employee WHERE name LIKE ' ';
SELECT * FROM employee WHERE homeaddr LIKE ' %';
SELECT * FROM employee WHERE homeaddr LIKE '% ';
7.
IS NULL (NULL)
：
IS [NOT] NULL
，'NOT' ， NOT 。
CREATE TABLE work(
id INT,
name VARCHAR(20),
gender VARCHAR(4),
info VARCHAR(50)
)
SELECT * FROM work WHERE info IS NULL;
SELECT * FROM work WHERE info IS NOT NULL;
8. AND
AND
，
：
1 AND 2 [... AND n]
，AND 。
SELECT * FROM employee
WHERE d_id<1004
AND gender LIKE ' ';
9. LIMIT
，
。
SELECT * FROM employee LIMIT 2;
SELECT * FROM employee LIMIT 2, 2;
10.
'^'
CREATE TABLE info(
id INT,
name VARCHAR(20)
)
INSERT INTO info VALUES(1, 'Aric');
INSERT INTO info VALUES(2, 'Eric');
INSERT INTO info VALUES(3, 'Jame');
INSERT INTO info VALUES(4, 'Jack');
INSERT INTO info VALUES(5, 'Lucy');
INSERT INTO info VALUES(6, 'Lily')
INSERT INTO info VALUES(7, 'Tom');
INSERT INTO info VALUES(8, 'aaa');
INSERT INTO info VALUES(9, 'dadaaa');
INSERT INTO info VALUES(10, '2323');
INSERT INTO info VALUES(11, 'bbdfec12');
INSERT INTO info VALUES(12, '212abc');
INSERT INTO info VALUES(13, 'werabc');
SELECT * FROM info WHERE name REGEXP '^L';
SELECT * FROM info WHERE name REGEXP '^aaa';
SELECT * FROM info WHERE name REGEXP '^L';
SELECT * FROM info WHERE name REGEXP '^aaa';
11.
SELECT * FROM info WHERE name REGEXP 'c$';
SELECT * FROM info WHERE name REGEXP 'aaa$';
12. '.'
SELECT * FROM info WHERE name REGEXP '^L..y$'
13.
([]) 。 ， 。 ， "[abc]" a、b、c 。
SELECT * FROM info WHERE name REGEXP '[ceo]';
SELECT * FROM info WHERE name REGEXP '[0-9]';
14.
'[^ ]' 。 info a w 。
SELECT * FROM info WHERE name REGEXP '[^a-w0-9]';
15.
。 ， 。 ， '|' 。 。
SELECT * FROM info WHERE name REGEXP 'ic';
SELECT * FROM info WHERE name REGEXP 'ic|ab|uc';
16. '*' '+'
,'*' '+' 。
，'+' ， '*' 。
SELECT * FROM info WHERE name REGEXP 'a*c';
SELECT * FROM info WHERE name REGEXP 'a+c';
17. {M} {M,N}
，' {M}' M ；
' {M,N}' M ， N
'ab{2}' 'ab' 。'
ab{2,4}' 'ab' ，
SELECT * FROM info WHERE name REGEXP 'a{3}';
SELECT * FROM info WHERE name REGEXP 'ab{1,3}';
COUNT()、SUM()、AVG()、MAX() MIN()。
COUNT() ；
SUM() ；
AVG() ；
MAX() ；
MIN() 。
1. COUNT
COUNT() 。 employee ， COUNT() 。 employee ， COUNT() 。
SELECT COUNT(*) FROM employee;
SELECT d_id, COUNT(*) from employee GROUP BY d_id;
2 .SUM()
SUM() 。 SUM() 。 ， SUM() 。
CREATE TABLE grade(
num INT NOT NULL,
course VARCHAR(10) NOT NULL,
score FLOAT
)
INSERT INTO grade VALUES
(1001, ' ', 80),
(1001, ' ', 90),
(1001, ' ', 85),
(1001, ' ', 95),
(1002, ' ', 88),
(1002, ' ', 90),
(1002, ' ', 89),
(1002, ' ', 90),
(1003, ' ', 80),
(1003, ' ', 98),
(1003, ' ', 85),
(1003, ' ', 95);
SELECT * FROM grade WHERE num=1001;
SELECT num, SUM(score) FROM grade WHERE num=1001;
SELECT num, SUM(score) FROM grade GROUP BY num;
3. AVG()
AVG() 。 AVG() 。 ， AVG() ， AVG() 。
SELECT AVG(age) FROM employee;
SELECT course, AVG(score) FROM grade GROUP BY course;
SELECT num, AVG(score) FROM grade GROUP BY num;
4 .MAX()
MAX() 。 MAX() 。 ， MAX() ， MAX() 。
SELECT MAX(age) FROM employee;
SELECT num, course, MAX(score) FROM grade GROUP BY course;
5 .MIN()
MIN() 。 MIN() 。 ， MIN() ， MIN() 。
SELECT MIN(age) FROM employee;
SELECT num, course, MIN(score) FROM grade GROUP BY course;