Shulou(Shulou.com)06/01 Report--

The following mainly brings you database knowledge and spl sentence-related handouts. I hope database knowledge and spl sentence-related handouts can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

The following is to implement some statements.

Write a sql statement and send it to the server for execution

-- you can write comments later

-- create a library, day16

CREATE DATABASE day16 DEFAULT CHARACTER SET utf8

Using day16 as a database

USE day16

Query all the tables in the day16 library

SHOW TABLES

-- demand: create a student table (id,name,age)

CREATE TABLE student (

-- Field name Field Type

Id INT

NAME VARCHAR (20)

Age INT

);

-- query the data in the table

SELECT * FROM student

-- query a table structure

DESC student

-- Delete student table

DROP TABLE student

-add an address field (gender varchar (2))

ALTER TABLE student ADD COLUMN gender VARCHAR (2)

-- modify the gender field type to varchar (3)

ALTER TABLE student MODIFY COLUMN gender VARCHAR (3)

-- modify gender field to sex field varchar (2)

ALTER TABLE student CHANGE COLUMN gender sex VARCHAR (2)

-- add an address field

ALTER TABLE student ADD COLUMN address VARCHAR (20)

-- delete the sex and address fields

ALTER TABLE student DROP COLUMN sex,DROP COLUMN address

Change the name of the student table to stu

ALTER TABLE stu RENAME TO student

-- View the data of the table

-- select field name (if all fields are queried *) from student

-- View table data

SELECT * FROM student

-insert 3 pieces of data into the table (insert all fields)

INSERT INTO student VALUES (1) 'Andy Lau', 50 'Hong Kong, China')

INSERT INTO student VALUES (2 'Eason Chan', 40 'Hong Kong, China')

INSERT INTO student VALUES (3 recordings' Han Hong', 50 pencils' Hebei')

Can we insert some fields directly when we need to insert all the fields? It can't be inserted.

INSERT INTO student VALUES (1) 'Jackie Chan', 60 'Hong Kong, China')

Point to the table that you only want to insert id field and name field. What should I do?

INSERT INTO student (id,NAME) VALUES (4female 'Guo Degang')

Change the age of all students to 50, modify the age of all students, and use very little.

UPDATE student SET age=50

Change the name of the student whose id is 3 to Jaycee Chan

UPDATE student SET NAME=' Jaycee Chan 'WHERE id=3

Modify several fields, change the name of the student whose id is 2 to Zhang Xueyou, and change the age to 60

UPDATE student SET NAME=' Jacky Cheung', age=60 WHERE id=2

-- deleting the whole table data is essentially deleted one by one, and the efficiency is relatively slow.

DELETE FROM student

-Delete data with an id of 3

DELETE FROM student WHERE id=3

-- use the truncate table table name to delete the whole table data, not delete one by one, but delete the whole table data directly, which is more efficient.

TRUNCATE TABLE student

-- what's the difference between delete from and truncate table, which delete a whole table?

-- the 1.delete from is deleted one by one, and truncate table directly kills the whole table data.

-- 2.delete from can delete a piece of data conditionally, while truncate table can only delete all table data, not conditional data.

-- 3.delete from cannot reset the self-growing primary key. Truncate table can reset the self-growing primary key.

-- query data

-- query all columns

SELECT * FROM student

-- query specified fields and query id,name

SELECT id,NAME FROM student

-- specify alias, name-- name and address-- address when querying

SELECT NAME AS 'name', address AS 'address' FROM student

As with aliases specified above can be omitted

SELECT NAME 'name' FROM student

-- add servlet,jsp field

ALTER TABLE student ADD COLUMN servlet INT,ADD COLUMN jsp INT

Add servlet and jsp scores to each piece of student data

UPDATE student SET servlet=50,jsp=60 WHERE id=1

UPDATE student SET servlet=60,jsp=70 WHERE id=2

UPDATE student SET servlet=70,jsp=80 WHERE id=3

Merge servlet and jsp columns to query the sum of each student's servlet and jsp scores

A feature of juxtaposition queries is that only fields of numeric types can be merged.

SELECT NAME 'name', (servlet+jsp) 'Total score' FROM student

Add a constant column to the query and add a constant column class to the student table-- java001

SELECT NAME 'name', address' address', 'java001' AS' class' FROM student

Find out where all the students in your class come from

SELECT NAME 'name', address' address' FROM student

Remove duplicate values to find out where each student comes from

SELECT DISTINCT address FROM student

-- another way to remove duplicate values

SELECT DISTINCT (address) FROM student

-- query students whose id is 1 and whose servlet score is equal to 50 (intersection and)

SELECT * FROM student WHERE id=1 AND servlet=50

-- query students whose id is 1 or come from Hong Kong, China (and or)

SELECT * FROM student WHERE id=1 OR address=' Hong Kong, China'

-- inquire about students whose servlet scores are greater than 60

SELECT * FROM student WHERE servlet > 60

-- query students whose jsp scores are less than or equal to 70

SELECT * FROM student WHERE jsp

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.